Help! A measure to compare the dates from two different columns.

[Anonymous]

Hello All,

I'm looking to compare the Start date of a row with the end date of the previous row using an if condition and display the result in a calculated column.

Here for example , if you take row 2 - I'm looking to compare Jan 10 with Feb 1.

Looking forward to your help.

Thanks.

[kentyler · Accepted solution]

Getting a value from the previous row is not something that is native to Power BI. But it is a very common request.

We use a work around. Since we always want just the previous row we add an index to our table (this assumes the table arrives in power bi in the proper sort order).

Once we have an index we can use it to "move back" to the previous row and get the date

Previous Start Date =

var cur_index = Dates[Index]

// Since we always want to move to the previous row

var prev_index = Dates[Index] - 1

// We ask for ALL() of the table, and then filter for the row that has the matching index

var prev_start_date = calculate(MAX(Dates[Start Date]),ALL(dates),Dates[Index] = prev_index)

//if there was no previous row, we get a blank

return prev_start_date

[Anonymous]

Thanks for your reply! I will work with index column.