cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

1 ACCEPTED SOLUTION
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

11 REPLIES 11
Anonymous
Not applicable

can you post some sample data that I can grab?

Frequent 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
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

Frequent 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 CurrentCompleted= 'Maintenance'[Finish Time]
RETURN

CALCULATE(
COUNTROWS(
FILTER( ALL ( 'Maintenance' ) ,
CurrentPark = 'Maintenance'[Park Name]
&& 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?

Anonymous
Not applicable

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

Frequent Visitor

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.

Anonymous
Not 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*/
)
)```
Frequent 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?

Anonymous
Not 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.

https://1drv.ms/u/s!AoQIGRpzoxRH0zK4D4Ue5nYGNGbj

Frequent Visitor

It's not that. I have multiple tasks, so what it's doing is calculating the time from "any" previous task. In order to fix it, I had to filter down to just the Mow task type. I think there's probably a way to add that to the filter in the calculation, but doing it in Power Query was quicker.

Anonymous
Not applicable

The index calculated column takes uses the  Park, Activity, and Date Completed to as the "filter" to count, so adding in new activities shouldnt matter.

I added in a generic "Activity 2" and still get the same result:

if you want, share the pbix file.  Not sure if you can but could be helpful.  Unless you are good?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors