0

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 ;)

Arseniy
  • 487
  • 4
  • 14

1 Answers1

0

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.

Rory
  • 959
  • 10
  • 22
  • To be clear, the Tabular engine has no separate Date type. It only knows Date/time, internally represented by a decimal number (can't recall if float or decimal) where the whole number portion represents the number of days since a reference date, and the decimal portion represents the time portion of a day. You can create a datetime field with the time portion set to midnight (all 0s past the decimal), but cannot set or transform a field to a date. – greggyb Dec 20 '15 at 23:33
  • I'm not sure that's the case: ClearDate in my table is a date, I calculate it from datetime with the formula: TRUNC(datetime-column, 0), i.e. I leave only the date portion. – Arseniy Dec 21 '15 at 08:55
  • Rory, the thing is that i'm already getting the numeric value, but it's the *same* for every cell... May be I confused you saying that the values stop calculating, but I meant all the *other* fields stop, but the *LastPurchaseDaysAgo* has value and it's the same everywhere... – Arseniy Dec 21 '15 at 08:58
  • I would guess that the numeric value for TODAY() is being returned everywhere. What happens when you try the following? = TODAY () - DATE ( YEAR ( myTable[ClearDate] ), MONTH ( myTable[ClearDate] ), DAY ( myTable[ClearDate] ) ) – Rory Dec 21 '15 at 13:24