Days between the first order and the second order

[yaronch7]

How to calculate the days between the first order and the second order

order table :

account#, year,qurter,month,day

11111      2016  Q3   April    10

11111      2016  Q3   April    15

I want to get

account  daye_betwen_purchas

111111                   5

I tried to use with Matrix or to calculate with LASTDATE function , but didnt worked well , what is the recomnded approach

[BhaveshPatel · Accepted solution]

Hi There, 

For such calculations, you should create a Index column in the Query Editor and Load the Query.

In the modelling stage, create a calculated column shown in the screenshot to view the expected result.

[GilbertQ · Accepted solution]

Could another option not be to unpivot the data, so that you get the first order date and second order date on one column?

Then you could easily use the DateDiff DAX syntax (https://msdn.microsoft.com/en-us/library/dn802538.aspx?f=255&MSPPError=-2147217396)

[BhaveshPatel · Accepted solution]

Hi There, 

For such calculations, you should create a Index column in the Query Editor and Load the Query.

In the modelling stage, create a calculated column shown in the screenshot to view the expected result.

[yaronch7]

works fine , Thanks !!