cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
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_IDCreated DateSent DateAccount_IDDuplicates
3033/1/20203/16/202041235960
3253/2/20203/13/202038003020
3282/1/20203/2/202034510870
3352/12/20203/3/202040836960
3363/1/20203/11/202033209160
3372/8/20203/17/202011835460
3402/9/20203/9/202037974730
3412/10/20203/3/202029216600
3442/11/20203/3/202011054870
3492/12/20203/25/20201844700
3502/13/20203/16/202021341530
3542/14/20203/2/202038048590
3552/15/20203/3/202026991550
3562/16/20203/3/202026504620
3572/17/20203/16/202026588590
3582/18/20203/12/202028273330
3632/19/20203/23/202030810940
3662/20/20203/20/202030566380
3472/21/20203/2/202037886460
3602/22/20203/4/202032099030
3642/23/20203/20/202037972300
3682/24/20203/30/202031981900
3622/25/20203/18/202032947491
3613/22/20203/28/202032947491
3672/27/20203/19/202031489211
3693/22/20203/28/202031489211

 

 

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
edhans
Super User
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.

2020-03-30 19_04_04-openclosedclaims - Power BI Desktop.png

 

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 I answer your question? Mark my post as a solution!
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

View solution in original post

5 REPLIES 5
amitchandak
Super User
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

edhans
Super User
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.

2020-03-30 19_04_04-openclosedclaims - Power BI Desktop.png

 

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 I answer your question? Mark my post as a solution!
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

@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 I answer your question? Mark my post as a solution!
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 , 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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors