Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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!
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThere 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
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
User | Count |
---|---|
94 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
146 | |
112 | |
73 | |
56 |