Date Difference between Rows in a Measure

[Tapemeasure]

Hi,

I'm trying to work out how to use a measure to caclualte the datedifference between rows

I've got an dataset that looks like (Date formatting is DD/MM/YYYY)

ID	StartDate
1	13/08/2017
2	13/08/2017
3	12/08/2017
4	24/08/2017

So far, I've added the following Measure using RANKX 

RankMeasure = RANKX(
  ALLSELECTED(TABLE),
     CALCULATE(
        FIRSTDATE(TABLE[StartDate])),,ASC,Dense)

When i add this to a table visualisation i get the follow

ID	StartDate	RankMeasure
1	13/08/2017	2
2	13/08/2017	2
3	12/08/2017	1
4	24/08/2017	3

So far so good, Now i'm trying to calculate the DateDifference (in days) between the rows so the result should look like

ID	StartDate	RankMeasure	DateDifference (days)
1	13/08/2017	2	1
2	13/08/2017	2	1
3	12/08/2017	1	0
4	24/08/2017	3	11

Any help would be great.

Thanks

[Tapemeasure · Accepted solution]

Hey everyone, thanks for your help, 

I managed to do it using the following measure (turns out, I don't need the rank column)

DaysBetweenFaults = 

var currentDate = SELECTEDVALUE(Table[StartDate])

var previousDate = CALCULATE(

 Max(Table[StartDate]),

 ALLSELECTED(Table),

 Table[StartDate] < currentDate)

return

 if(not ISBLANK(DATEDIFF(previousDate,currentDate,DAY)),DATEDIFF(previousDate,currentDate,DAY),0)

[Anonymous]

Hi @Tapemeasure 

I create a Calculated Column to rank StartDate:

Rank = RANKX('Table','Table'[StartDate],,ASC,Dense)

And I build a Day column:

Day = DAY('Table'[StartDate])

Then I use a measure to achieve your goal:

DateDifference (days) =

IF(MAX('Table'[Rank])=1,

0,

MAX('Table'[Day])-CALCULATE(VALUES('Table'[Day]),FILTER(ALL('Table'),'Table'[Rank]=MAX('Table'[Rank])-1)))

Result:

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EbLGEcfcu9tEsC0r...

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@Tapemeasure , If IS/Rank are continous

new column = datediff(Table[Date], maxx(filter(Table,Table[ID] =earlier(Table[ID])-1) ,Table[Date]),day)

// non-continous 
new column 2 =
var _ min = maxx(filter(Table,Table[ID] < earlier(Table[ID])-1) ,Table[ID])
return
datediff(Table[Date], minx(filter(Table,Table[ID] =_min) ,Table[Date]),day)

Both Measure and column methods is disuccsed here too

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...