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
can you post some sample data that I can grab?
- nickintosh7 years agoFrequent Visitor
Park Activity Completed Alberson Park Mow Edge Trim Blow 5/6/2018 16:16 Alberson Park Mow Edge Trim Blow 5/23/2018 18:38 Alberson Park Mow Edge Trim Blow 6/15/2018 18:05 Alberson Park Mow Edge Trim Blow 6/18/2018 16:45 Alberson Park Mow Edge Trim Blow 6/25/2018 9:00 Alberson Park Mow Edge Trim Blow 6/25/2018 9:00 Alberson Park Mow Edge Trim Blow 7/23/2018 17:24 Alberson Park Mow Edge Trim Blow 7/25/2018 18:06 Alberson Park Mow Edge Trim Blow 8/10/2018 17:27 Alberson Park Mow Edge Trim Blow 8/27/2018 17:32 Alberson Park Mow Edge Trim Blow 9/17/2018 18:48 Alberson Park Mow Edge Trim Blow 11/6/2018 13:00 Alberson Park Mow Edge Trim Blow 11/6/2018 13:00 Alcy-Samuels Park Mow Edge Trim Blow 3/28/2018 14:02 Alcy-Samuels Park Mow Edge Trim Blow 5/2/2018 23:34 Alcy-Samuels Park Mow Edge Trim Blow 5/2/2018 23:36 Alcy-Samuels Park Mow Edge Trim Blow 5/8/2018 23:30 Alcy-Samuels Park Mow Edge Trim Blow 6/13/2018 19:36 Alcy-Samuels Park Mow Edge Trim Blow 6/20/2018 18:31 Alcy-Samuels Park Mow Edge Trim Blow 7/10/2018 18:33 Alcy-Samuels Park Mow Edge Trim Blow 7/24/2018 18:33 Alcy-Samuels Park Mow Edge Trim Blow 8/7/2018 18:32 Alcy-Samuels Park Mow Edge Trim Blow 8/24/2018 19:26 Alcy-Samuels Park Mow Edge Trim Blow 9/13/2018 19:23 Alcy-Samuels Park Mow Edge Trim Blow 9/26/2018 19:26 Alcy-Samuels Park Mow Edge Trim Blow 10/17/2018 19:25 Alcy-Samuels Park Mow Edge Trim Blow 10/29/2018 19:26 Alcy-Warren Park Mow Edge Trim Blow 3/28/2018 14:07 Alcy-Warren Park Mow Edge Trim Blow 5/7/2018 19:32 Alcy-Warren Park Mow Edge Trim Blow 5/7/2018 19:34 Alcy-Warren Park Mow Edge Trim Blow 5/9/2018 23:31 Alcy-Warren Park Mow Edge Trim Blow 6/6/2018 19:35 Alcy-Warren Park Mow Edge Trim Blow 6/19/2018 18:37 Alcy-Warren Park Mow Edge Trim Blow 7/3/2018 18:31 Alcy-Warren Park Mow Edge Trim Blow 7/23/2018 18:34 Alcy-Warren Park Mow Edge Trim Blow 8/6/2018 18:31 Alcy-Warren Park Mow Edge Trim Blow 8/24/2018 19:21 Alcy-Warren Park Mow Edge Trim Blow 9/10/2018 19:29 Alcy-Warren Park Mow Edge Trim Blow 9/27/2018 19:31 Alcy-Warren Park Mow Edge Trim Blow 10/15/2018 19:25 Alcy-Warren Park Mow Edge Trim Blow 10/29/2018 19:27 Alonzo Weaver Park Mow Edge Trim Blow 3/28/2018 15:09 Alonzo Weaver Park Mow Edge Trim Blow 6/25/2018 9:00 Alonzo Weaver Park Mow Edge Trim Blow 6/25/2018 9:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park Mow Edge Trim Blow 11/6/2018 13:00 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 3/28/2018 15:14 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 4/18/2018 19:32 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 4/25/2018 19:40 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 5/17/2018 19:30 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 5/24/2018 19:30 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 6/6/2018 18:30 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 7/5/2018 18:34 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 7/30/2018 17:01 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 8/6/2018 18:30 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 8/23/2018 19:31 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 9/10/2018 19:38 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 10/17/2018 19:35 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 10/18/2018 19:37 Alonzo Weaver Park (W. Junction) Mow Edge Trim Blow 10/26/2018 19:39 American Way Park Mow Edge Trim Blow 3/23/2018 7:00 American Way Park Mow Edge Trim Blow 4/9/2018 7:00 American Way Park Mow Edge Trim Blow 4/13/2018 15:30 American Way Park Mow Edge Trim Blow 4/26/2018 7:00 American Way Park Mow Edge Trim Blow 5/3/2018 5:00 American Way Park Mow Edge Trim Blow 5/13/2018 7:00 American Way Park Mow Edge Trim Blow 5/30/2018 7:00 American Way Park Mow Edge Trim Blow 6/18/2018 7:00 American Way Park Mow Edge Trim Blow 7/15/2018 17:58 American Way Park Mow Edge Trim Blow 7/25/2018 18:19 American Way Park Mow Edge Trim Blow 8/7/2018 13:25 American Way Park Mow Edge Trim Blow 8/25/2018 18:37 American Way Park Mow Edge Trim Blow 9/17/2018 17:58 American Way Park Mow Edge Trim Blow 9/20/2018 19:39 American Way Park Mow Edge Trim Blow 10/18/2018 16:33 - Anonymous7 years agoNot applicable
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
- nickintosh7 years agoFrequent Visitor
Thank you! Oh, man, I think I'm so close to being there. My column names are slightly different, but here's my code for the first part:
Index = VAR CurrentPark= 'Maintenance'[Park Name] Var CurrentAct= 'Maintenance'[Maintenance Task] VAR CurrentCompleted= 'Maintenance'[Finish Time] RETURN CALCULATE( COUNTROWS( FILTER( ALL ( 'Maintenance' ) , CurrentPark = 'Maintenance'[Park Name] && CurrentAct = 'Maintenance'[Maintenance Task] && CurrentCompleted >= 'Maintenance'[Finish Time] ) ) )It seems to work, but I get some weird index numbers when I create a table to check.
I'm assuming I'm messing something up. Additionally, I'm not sure how to de-dupe, but (in theory) the data shouldn't have dupes because it's coming from an ESRI app that doesn't allow for duplicates. That said, when I try to run a "Delete Duplicates" in Power Query, it's obviously looking for a true "all columns match" duplicate and I can't seem to find a way to edit that.
Thoughts?