DAX to create a column from difference of dates within the group

[Anonymous]

Hi,

As per the below snippet, I need to create a Slip days, based on Baseline Date. For every Project ID/Name, there is baseline date and they will add new rows with different baseline date with some change. Now I want to find the difference in days, for new baseline date with the very first baseline date for a specific project. Example snippet given below,

Thank you in advance!

Regards,

Jyoti Pawar

[ryan_mayu · Accepted solution]

@Anonymous 

you can create a column

Column = 'Table'[Baseline Date]-CALCULATE(min('Table'[Baseline Date]),ALLEXCEPT('Table','Table'[Project ID],'Table'[Project Name]))

or a measure

Measure = DATEDIFF(CALCULATE(min('Table'[Baseline Date]),ALLEXCEPT('Table','Table'[Project ID],'Table'[Project Name])),MAX('Table'[Baseline Date]),DAY)

pls see the attachment below

[ryan_mayu · Accepted solution]

@Anonymous 

you can create a column

Column = 'Table'[Baseline Date]-CALCULATE(min('Table'[Baseline Date]),ALLEXCEPT('Table','Table'[Project ID],'Table'[Project Name]))

or a measure

Measure = DATEDIFF(CALCULATE(min('Table'[Baseline Date]),ALLEXCEPT('Table','Table'[Project ID],'Table'[Project Name])),MAX('Table'[Baseline Date]),DAY)

pls see the attachment below

[Anonymous]

@ryan_mayu 

Thank you for your previous reply, there is one small twist in the original requirement.

Now to calculate the Slip days, we need to consider Report date, MIN(Report date) should be considered and associated Basline Date and the difference we need to consider.

Difference is still calcuated on Basline Date, but if you look a the project B, last value is negative, we did not consider MIN of Basline Date, we took MIN of Report date which is 1/1/2022 and associated Basline is 2/15/2022 and then got the difference.

I am little new to DAX space, appreciate your help!

Thank you!

Regards,

Jyoti

[ryan_mayu · Accepted solution]

@Anonymous 

Pls see the attachment below

[Anonymous]

Thank you so much for your help!