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

Anonymous
Not applicable

## Calculating duration between two dates in different rows based on duplicate value

Hi, I want to calculate the duration (# of days) between two fields based on the same value.

For example, I have data:

 Case_ID Created Date Sent Date Account_ID Duplicates 303 3/1/2020 3/16/2020 4123596 0 325 3/2/2020 3/13/2020 3800302 0 328 2/1/2020 3/2/2020 3451087 0 335 2/12/2020 3/3/2020 4083696 0 336 3/1/2020 3/11/2020 3320916 0 337 2/8/2020 3/17/2020 1183546 0 340 2/9/2020 3/9/2020 3797473 0 341 2/10/2020 3/3/2020 2921660 0 344 2/11/2020 3/3/2020 1105487 0 349 2/12/2020 3/25/2020 184470 0 350 2/13/2020 3/16/2020 2134153 0 354 2/14/2020 3/2/2020 3804859 0 355 2/15/2020 3/3/2020 2699155 0 356 2/16/2020 3/3/2020 2650462 0 357 2/17/2020 3/16/2020 2658859 0 358 2/18/2020 3/12/2020 2827333 0 363 2/19/2020 3/23/2020 3081094 0 366 2/20/2020 3/20/2020 3056638 0 347 2/21/2020 3/2/2020 3788646 0 360 2/22/2020 3/4/2020 3209903 0 364 2/23/2020 3/20/2020 3797230 0 368 2/24/2020 3/30/2020 3198190 0 362 2/25/2020 3/18/2020 3294749 1 361 3/22/2020 3/28/2020 3294749 1 367 2/27/2020 3/19/2020 3148921 1 369 3/22/2020 3/28/2020 3148921 1

The duplicates show that there are two different cases for the same account. I would like to find out if the account is the same then what is the duration between when the case was created on the second case - report sent date for the first case. The order will be based on the created date. The goal is to understand if the same account is coming back to create another case within 90 days of when their report was sent.

For example:

Account 3294749

1st Case = 362 (created on 2/25, report sent 3/18/2020)

2nd Case = 361 (created on 3/22, report sent 3/28/2020)

Expected Outcome (new column):

361 Created Date - 362 Report Sent Date = 4 days

Thank you!

1 ACCEPTED SOLUTION
Super User

This calculated column will do the trick:

``````Date Difference =
VAR CurrentAccount = [Account_ID]
VAR CurrentRecordSet =
FILTER(
'DateDiff Table',
'DateDiff Table'[Account_ID] = CurrentAccount
)
VAR AccountCount =
COUNTX(
CurrentRecordSet,
'DateDiff Table'[Account_ID]
)
VAR CreatedDate =
IF(
AccountCount > 1,
MAXX(
CurrentRecordSet,
'DateDiff Table'[Created Date]
),
0
)
VAR SentDate =
IF(
AccountCount > 1,
MINX(
CurrentRecordSet,
'DateDiff Table'[Sent Date]
),
0
)
VAR DateDifference =
DATEDIFF(
SentDate,
CreatedDate,
DAY
)
RETURN
DateDifference
``````

It returns zero if there is only one record for an account.

If you want it to return the dates between sent and created even if there is only one record, get rid of the IF() function. So for example:

``````VAR SentDate =
IF(
AccountCount > 1,
MINX(
CurrentRecordSet,
'DateDiff Table'[Sent Date]
),
0
)``````

becomes

``````VAR SentDate =
MINX(
CurrentRecordSet,
'DateDiff Table'[Sent Date]
)``````

Same logic for the CreatedDate variable. You didn't specify in your OP, so wasn't sure how you wanted those handled.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
5 REPLIES 5
Super User

There is an example of the workday in this file. Please take distinct count of date where workday =1

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Super User

This calculated column will do the trick:

``````Date Difference =
VAR CurrentAccount = [Account_ID]
VAR CurrentRecordSet =
FILTER(
'DateDiff Table',
'DateDiff Table'[Account_ID] = CurrentAccount
)
VAR AccountCount =
COUNTX(
CurrentRecordSet,
'DateDiff Table'[Account_ID]
)
VAR CreatedDate =
IF(
AccountCount > 1,
MAXX(
CurrentRecordSet,
'DateDiff Table'[Created Date]
),
0
)
VAR SentDate =
IF(
AccountCount > 1,
MINX(
CurrentRecordSet,
'DateDiff Table'[Sent Date]
),
0
)
VAR DateDifference =
DATEDIFF(
SentDate,
CreatedDate,
DAY
)
RETURN
DateDifference
``````

It returns zero if there is only one record for an account.

If you want it to return the dates between sent and created even if there is only one record, get rid of the IF() function. So for example:

``````VAR SentDate =
IF(
AccountCount > 1,
MINX(
CurrentRecordSet,
'DateDiff Table'[Sent Date]
),
0
)``````

becomes

``````VAR SentDate =
MINX(
CurrentRecordSet,
'DateDiff Table'[Sent Date]
)``````

Same logic for the CreatedDate variable. You didn't specify in your OP, so wasn't sure how you wanted those handled.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi @edhans ! Thank you for your reply, your column has exactly what I am looking for. I'm not sure why I am getting the 'Token Eof Expected' error where the CurrentAccount expression is. Any thoughts?

VAR CurrentAccount = [Account_ID]

Here are the data types that I am using:

Case_ID = Text

Account_ID = Text

Sent Date = Date

Report Date = Date

Thank you again!

Jenny

Super User

@Anonymous I'd need to see a screenshot of the error. If you are entereing this as a calculated column, it should work. THat syntax will not work as a measure.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Super User

@Anonymous , See if this can help

``Working Days = CALCULATE(DISTINCTCOUNT('Date'[Working date]),VALUES(Sheet1),filter(all('Date'),'Date'[Date]>=[Min Created Date] && 'Date'[Date] <=[Max Send date]),NOT(ISBLANK('Date'[Working date])))``

File attached after signature

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.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors