Forum Discussion
Calculate days between 2 events
- 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
It's not that. I have multiple tasks, so what it's doing is calculating the time from "any" previous task. In order to fix it, I had to filter down to just the Mow task type. I think there's probably a way to add that to the filter in the calculation, but doing it in Power Query was quicker.
The index calculated column takes uses the Park, Activity, and Date Completed to as the "filter" to count, so adding in new activities shouldnt matter.
I added in a generic "Activity 2" and still get the same result:
if you want, share the pbix file. Not sure if you can but could be helpful. Unless you are good?