Re: Help with DateDiff

GJUDGE · ‎10-26-2021

Probably a straightforward one for most, but how to do I write a measure to caluclate the movement in days of each construction project from one month to the next? So for example, the movement in construction date for project A would show 30 days?

It would be easier for me to have separate measures for changes in construction start date and commissioning date.

Also is there any way to make this dynamic by referring to the max reporting period and one less than this as opposed to using Sept-21 and Aug-21 in the measure?

Thanks,

Project	Output	Construction Start Date	Commissioning Date	Reporting Period
A	50	01/01/2023	01/06/2023	Aug-21
B	25	01/04/2023	07/04/2023	Aug-21
C	50	01/05/2023	01/07/2023	Aug-21
A	50	01/02/2023	01/06/2023	Sep-21
B	25	01/04/2023	07/04/2023	Sep-21
C	50	01/05/2023	01/06/2023	Sep-21

v-eqin-msft · ‎10-29-2021

Hi @GJUDGE ,

the COD date hasn't changed in the most recent two months

The latest date is 2025-Feb, so if there do not exist COD= 2025-Jan and 2024-Dec, then datediff should be 0 ?

Measure 2 = 
var _last=CALCULATE(MAX('Table'[Construction Start Date]),ALLEXCEPT('Table','Table'[Project]))
var _pre= MAXX(FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project]) && [Construction Start Date]<_last),[Construction Start Date])
return IF(DATEDIFF(_pre,_last,MONTH)<=2,DATEDIFF( _pre,_last,DAY),0)

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

GJUDGE · ‎10-29-2021

@v-eqin-msft Yes, the COD date hasn't changed, but even if this is the case I need the measure to only compare the COD dates of the last two months. Even is this is zero. For example, in project B in the test data I provided, I should still see a return of zero. Basically what I want to do is COD Date (Filltered reporting period for the latest month) minus COD Date (filtered for reporting period last month minus 1 month).

v-eqin-msft · ‎10-29-2021

Hi @GJUDGE ,

I have added a row to simply reproduce data as you said your actual data contains many months data.

My first measure returns the date diff for each changed .

Below will return the diff between the latest two date:

Measure 2 = 
var _last=CALCULATE(MAX('Table'[Construction Start Date]),ALLEXCEPT('Table','Table'[Project]))
var _pre= MAXX(FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project]) && [Construction Start Date]<_last),[Construction Start Date])
return DATEDIFF(_pre,_last,DAY)

If it is still not your expected,please show me what you want .

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

GJUDGE · ‎10-29-2021

@v-eqin-msft I really appreciate your help. When I try your latest measure on my actual data I still get the incorrect result. Below is project A. COD date is the same as construction start date. When I use either of your two measures, I get a difference of 153 days when this should be zero (i.e. the COD date hasn't changed in the most recent two months). I hope that makes sense!

v-eqin-msft · ‎10-28-2021

Hi @GJUDGE ,

Please try:

Measure = 
var _pre=MAXX(FILTER(ALL('Table'),[Project]=MAX('Table'[Project]) && [Construction Start Date]<MAX('Table'[Construction Start Date])),[Construction Start Date])
return DATEDIFF(_pre,MAX('Table'[Construction Start Date]),DAY)

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

GJUDGE · ‎10-29-2021

@v-eqin-msft This almost gets me there! The only other complexity is that my actual data contains many months data. Using your measure it appears to be comparing the last and first dates rather than the last two months. is there any way to modify your measure?

lbendlin · ‎10-27-2021

please explain how you get to the 120.

GJUDGE · ‎10-27-2021

@lbendlin In August, project A was planned to start construction on 01/01/2023. In September this date had slipped to 01/05/2023. I.e. it had moved back 120 days.