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...
  • 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