Forum Discussion
nickintosh
7 years agoFrequent Visitor
Calculate days between 2 events
I've got a table of maintenance activities and I'm struggling to figure out how to use DATEDIFF to calculate the number of days between the grass cuttings grouped by parks. Here's a sample of the dat...
- Anonymous7 years ago
Here's what I came up with. There's a few steps, but not to painful.
Step 1:
- Get the data into Power Query.
- Figure out a way to remove exact duplicates. Meaning same Park, Activity, and Completed Date/Time
- Create a copy of the Completed column, and change to data type to Whole Number
Step 2:
- Load that into the data model
- Add an "Index" column so we know what the previous date was:
Index = VAR CurrentPark= 'Calculate Dates Between'[Park] Var CurrentAct= 'Calculate Dates Between'[Activity] VAR CurrentCompleted= 'Calculate Dates Between'[Completed] RETURN CALCULATE( COUNTROWS( FILTER( ALL ( 'Calculate Dates Between' ) , CurrentPark = 'Calculate Dates Between'[Park] && CurrentAct = 'Calculate Dates Between'[Activity] && CurrentCompleted >= 'Calculate Dates Between'[Completed] ) ) ) - Now we can write a measure since we have the data we need in the correct format:
Days Since Last Cut = IF ( NOT ( HASONEVALUE('Calculate Dates Between'[Index])), BLANK(), /* this will put blank if there is more than 1 index (i.e. subtotals/totals*/ IF( MAX('Calculate Dates Between'[Index]) <>1, /*do not want a value on the 1st date, so will put "First cut"*/ MAX( 'Calculate Dates Between'[Completed - Whole Number])- /*Current Whole Date in the current filter context*/ CALCULATE( MAX( 'Calculate Dates Between'[Completed - Whole Number]), FILTER( ALL( 'Calculate Dates Between'), 'Calculate Dates Between'[Index] = MAX('Calculate Dates Between'[Index])-1 ), /*want to filter that whole number column we created by taking the current index and going back one */ VALUES('Calculate Dates Between'[Park]) /*need to keep the filter on Park in play*/ ), "First Cut" /*Value for 1st date, could be anything )Seems like a lot (and it is!) but when broken down it's not so bad.....
Here's the final output:
Hopefully that makes sense, but fire away any questions