Forum Discussion

nickintosh's avatar
nickintosh
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    7 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

     

Anonymous's avatar
Anonymous
Not applicable
7 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

 

nickintosh's avatar
nickintosh
Frequent Visitor
7 years ago

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?