CODEWITHSUNDEEP
excelpowerpivotdata-analysisdax
Arseniy
Arseniy

Reputation: 487

PowerPivot calculate date difference in the current context

I'd like to segment my customers on their purchasing activity (active, sleeping, dead). To do so, I need to find the number of the days between the last purchase date and current context (because the customer can be sleeping in one period and active in another).

So I add the calculated field to the Customers table:

LastPurchaseDate:= LASTDATE(purchases[ClearDate])

and it works fine.

The problem occurs when I try to calculate the days difference between two days:

LastPurchaseDaysAgo:= CALCULATE(1*(TODAY()-[LastPurchaseDate]))

In the result I get the same value everywhere (customers in rows and date hierarchy in columns) and the other fields added to the pivot stop calculating.

  1. How do I fix the problem with the same value for every row? I understand that when I'm using TODAY() for diff calculation, I should get the same value for each customer over all periods, but I also get the same value for all the customers.
  2. What should I use instead of TODAY() to calc the difference for the current date context? I tried to use Calendar[Datekey] - my dates table, but PowerPivot didn't like it...

Thanks ;)

Upvotes: 0

Views: 585

Answers (1)

Rory
Rory

Reputation: 969

Check that ClearDate is a date not a datetime. If it is a datetime you can transform it in DAX into a date by DATE(YEAR(myTable([ClearDate]),MONTH(myTable([ClearDate]),DAY(myTable([ClearDate])). Then you should be able to subtract it from TODAY() to get a numeric value.

Upvotes: 0

Related Questions