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 data

 

Ideally, what I'd like to do is have a fourth column that says "Days since last cut". I've tried to modify code I've seen using DATEDIFF and some sort of placeholder VAR, but they all seem to assume that you've only got one "Park" value, so it gives me the number of days since ANY park was cut. Any help would be appreciated. 

 

 

  • 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

     

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    can you post some sample data that I can grab?  

    • nickintosh's avatar
      nickintosh
      Frequent Visitor
      ParkActivityCompleted
      Alberson ParkMow Edge Trim Blow5/6/2018 16:16
      Alberson ParkMow Edge Trim Blow5/23/2018 18:38
      Alberson ParkMow Edge Trim Blow6/15/2018 18:05
      Alberson ParkMow Edge Trim Blow6/18/2018 16:45
      Alberson ParkMow Edge Trim Blow6/25/2018 9:00
      Alberson ParkMow Edge Trim Blow6/25/2018 9:00
      Alberson ParkMow Edge Trim Blow7/23/2018 17:24
      Alberson ParkMow Edge Trim Blow7/25/2018 18:06
      Alberson ParkMow Edge Trim Blow8/10/2018 17:27
      Alberson ParkMow Edge Trim Blow8/27/2018 17:32
      Alberson ParkMow Edge Trim Blow9/17/2018 18:48
      Alberson ParkMow Edge Trim Blow11/6/2018 13:00
      Alberson ParkMow Edge Trim Blow11/6/2018 13:00
      Alcy-Samuels ParkMow Edge Trim Blow3/28/2018 14:02
      Alcy-Samuels ParkMow Edge Trim Blow5/2/2018 23:34
      Alcy-Samuels ParkMow Edge Trim Blow5/2/2018 23:36
      Alcy-Samuels ParkMow Edge Trim Blow5/8/2018 23:30
      Alcy-Samuels ParkMow Edge Trim Blow6/13/2018 19:36
      Alcy-Samuels ParkMow Edge Trim Blow6/20/2018 18:31
      Alcy-Samuels ParkMow Edge Trim Blow7/10/2018 18:33
      Alcy-Samuels ParkMow Edge Trim Blow7/24/2018 18:33
      Alcy-Samuels ParkMow Edge Trim Blow8/7/2018 18:32
      Alcy-Samuels ParkMow Edge Trim Blow8/24/2018 19:26
      Alcy-Samuels ParkMow Edge Trim Blow9/13/2018 19:23
      Alcy-Samuels ParkMow Edge Trim Blow9/26/2018 19:26
      Alcy-Samuels ParkMow Edge Trim Blow10/17/2018 19:25
      Alcy-Samuels ParkMow Edge Trim Blow10/29/2018 19:26
      Alcy-Warren ParkMow Edge Trim Blow3/28/2018 14:07
      Alcy-Warren ParkMow Edge Trim Blow5/7/2018 19:32
      Alcy-Warren ParkMow Edge Trim Blow5/7/2018 19:34
      Alcy-Warren ParkMow Edge Trim Blow5/9/2018 23:31
      Alcy-Warren ParkMow Edge Trim Blow6/6/2018 19:35
      Alcy-Warren ParkMow Edge Trim Blow6/19/2018 18:37
      Alcy-Warren ParkMow Edge Trim Blow7/3/2018 18:31
      Alcy-Warren ParkMow Edge Trim Blow7/23/2018 18:34
      Alcy-Warren ParkMow Edge Trim Blow8/6/2018 18:31
      Alcy-Warren ParkMow Edge Trim Blow8/24/2018 19:21
      Alcy-Warren ParkMow Edge Trim Blow9/10/2018 19:29
      Alcy-Warren ParkMow Edge Trim Blow9/27/2018 19:31
      Alcy-Warren ParkMow Edge Trim Blow10/15/2018 19:25
      Alcy-Warren ParkMow Edge Trim Blow10/29/2018 19:27
      Alonzo Weaver ParkMow Edge Trim Blow3/28/2018 15:09
      Alonzo Weaver ParkMow Edge Trim Blow6/25/2018 9:00
      Alonzo Weaver ParkMow Edge Trim Blow6/25/2018 9:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver ParkMow Edge Trim Blow11/6/2018 13:00
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow3/28/2018 15:14
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow4/18/2018 19:32
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow4/25/2018 19:40
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow5/17/2018 19:30
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow5/24/2018 19:30
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow6/6/2018 18:30
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow7/5/2018 18:34
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow7/30/2018 17:01
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow8/6/2018 18:30
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow8/23/2018 19:31
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow9/10/2018 19:38
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow10/17/2018 19:35
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow10/18/2018 19:37
      Alonzo Weaver Park (W. Junction)Mow Edge Trim Blow10/26/2018 19:39
      American Way ParkMow Edge Trim Blow3/23/2018 7:00
      American Way ParkMow Edge Trim Blow4/9/2018 7:00
      American Way ParkMow Edge Trim Blow4/13/2018 15:30
      American Way ParkMow Edge Trim Blow4/26/2018 7:00
      American Way ParkMow Edge Trim Blow5/3/2018 5:00
      American Way ParkMow Edge Trim Blow5/13/2018 7:00
      American Way ParkMow Edge Trim Blow5/30/2018 7:00
      American Way ParkMow Edge Trim Blow6/18/2018 7:00
      American Way ParkMow Edge Trim Blow7/15/2018 17:58
      American Way ParkMow Edge Trim Blow7/25/2018 18:19
      American Way ParkMow Edge Trim Blow8/7/2018 13:25
      American Way ParkMow Edge Trim Blow8/25/2018 18:37
      American Way ParkMow Edge Trim Blow9/17/2018 17:58
      American Way ParkMow Edge Trim Blow9/20/2018 19:39
      American Way ParkMow Edge Trim Blow10/18/2018 16:33
      • Anonymous's avatar
        Anonymous
        Not 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