Forum Discussion
Help with DateDiff
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 |
10 Replies
please explain how you get to the 120.
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.Anonymous 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?
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.Anonymous 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!
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.Anonymous 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).
