Calculated column with days between previous dates

[digitalPlay]

I need to get the count of days between 2 dates (previous dates per row). It sort of works unless there are same dates for different categories then it will get the last non repeating date as the subtrahend. I would like to have a result where it will blank or zero for the 2nd repeating date. Any help is much appreciated 🙂

This is what I currently use:

var temp = TOPN(1,

    FILTER('Run Rate', 'Run Rate'[Project ID] = EARLIER('Run Rate'[Project ID])

    && 'Run Rate'[Milestone Date] < EARLIER( 'Run Rate'[Milestone Date] )

),

'Run Rate'[Milestone Date])

return

DATEDIFF(MINX(temp,'Run Rate'[Milestone Date]), 'Run Rate'[Milestone Date],DAY)

[v-yueyunzh-msft · Accepted solution]

Hi , @digitalPlay 

According to your description, you want to Calculated column with days between previous dates and you want to the previous same date show 0.

For your requirement, this needs to judge the row number, so we need to sort the columns in Power Query first and then create an Index column to meet the requirement.

First, we need to sort the two columns in Power Query Editor:

Then we need to add an index column in it :

Then we can modify your dax code like this:

Column = var temp = TOPN(1,
    FILTER('Run Rate', 'Run Rate'[Project ID] = EARLIER('Run Rate'[Project ID])
    && 'Run Rate'[Milestone Date] <= EARLIER( 'Run Rate'[Milestone Date] ) && 'Run Rate'[Index]<EARLIER('Run Rate'[Index])
),
'Run Rate'[Index])

return
DATEDIFF(MINX(temp,'Run Rate'[Milestone Date]), 'Run Rate'[Milestone Date],DAY)

Then we can get the result as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[digitalPlay]

awesome! thank you so much for your time and genius! you're a big help! 🙂