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
There are definitely dupes in the raw data:
that's why the Index gets messed up there. Because it should be 6, but since there are two records, it adds them together to get 12 and then things get wonky.
To remove the dupes in PQ, I mergeed those three columns, and then used the remove duplicates in that column. Then you can actually delete that column out and everything will still work.
Probably could use something like distinctcount, but I'd prefer to clean up the data in PQ
When I try to run the measure from the second half
Days Since Last Cut =
IF (
NOT ( HASONEVALUE('Maintenance'[Index])),
BLANK(), /* this will put blank if there is more than 1 index (i.e. subtotals/totals*/
IF( MAX('Maintenance'[Index]) <>1, /*do not want a value on the 1st date, so will put "First cut"*/
MAX( 'Maintenance'[Finish Time Whole Number])- /*Current Whole Date in the current filter context*/
CALCULATE(
MAX( 'Maintenance'[Finish Time Whole Number]),
FILTER(
ALL( 'Maintenance'),
'Maintenance'[Index] = MAX('Maintenance'[Index])-1
), /*want to filter that whole number column we created by taking the current index and going back one */
VALUES('Maintenance'[Park Name]) /*need to keep the filter on Park in play*/
),
"First Cut" /*Value for 1st date, could be anything
)))I get an end of error message:
The end of the input was reached.
- Anonymous7 years agoNot applicable
This one should work. Forget to add */ in the last comment to close it off. So DAX thought the comment was still going. Sorry about that:
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*/ ) )- nickintosh7 years agoFrequent Visitor
Ok. So, now the Index works fine and the calculation seems to be working fine, but it's coming up with weird negative numbers. Disclaimer, in the original data set, there were situations where the field techs would hit the button twice, so we'd have multiple finish times on one day. But, I made a duplicate of the finish time column as a date only and used it in the dedupe field. So, the index looks right, but it gives me weirdness. See below:
Any ideas?
- Anonymous7 years agoNot applicable
The duplicates need to be removed, which I'm not sure is happening on your end. I do not see those negative numbers:
I attached the pbix file below. Take a look at the query in Power Query to see what I did.