Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Greetings!
I am writing to seek your help to calculate pick-up time using DAX.
I tried few solutions online, however, it doesn't consider the case identifier while calculating the time difference between two rows. I have the below dataset
Index | Case Identifier | Steps | Final Outcome | Step Started | Step Ended | Pick up Time (Step Ended - Step Started time on the next stage) |
1 | 27771 | Step1 | Step 1 Completed | 6/14/21 16:30 | 6/14/21 17:00 | 4.0 |
2 | 12696 | Step1 | Step 1 Completed | 6/11/21 12:30 | 6/11/21 13:00 | 8.0 |
3 | 30997 | Step1 | Step 1 Completed | 6/11/21 15:00 | 6/11/21 17:00 | 4.0 |
4 | 27771 | Step 2 | Rejected Back to Step 1 | 6/15/21 9:00 | 6/15/21 10:00 | 15.5 |
5 | 12696 | Step 2 | Rejected Back to Step 1 | 6/14/21 9:00 | 6/14/21 10:00 | 17.0 |
6 | 30997 | Step 2 | Rejected Back to Step 1 | 6/13/21 20:00 | 6/13/21 20:00 | 1.5 |
7 | 12696 | Step1 | Step 1 Completed | 6/15/21 15:00 | 6/15/21 16:00 | |
8 | 27771 | Step1 | Step 1 Completed | 6/16/21 13:30 | 6/16/21 14:00 | |
9 | 30997 | Step1 | Step 1 Completed | 6/14/21 10:30 | 6/17/21 0:00 |
This is how I want to create a relationship to derive pick-up time:
Pick Up time calculation would have to consider the below logic:
- Time calculation should only be considered until the last working day & the next working day if the case is started (picked) on a weekend or on a bank holiday (in Ireland).
- Only working hours (9 am - 9 PM) must be considered for the calculation of pick-up time.
- If a case is worked from start - end on a weekend, the pick-up time for that case must be shown as 0.
I am struggling very much to create a relationship between the dates and case identifiers to derive the desired result (pick-up time).
Can I please ask for your help with my project.
Sincerely,
Raj
Solved! Go to Solution.
Hi @Anonymous ,
First create a calendar table;
Then create 3 columns as below:
_next step start =
var _index=CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[Case Identifier]=EARLIER('Table'[Case Identifier])&&'Table'[Index]>EARLIER('Table'[Index])))
Return
CALCULATE(MAX('Table'[Step Started]),FILTER('Table','Table'[Index]=_index))
Turnaround =
VAR _weekdaystart=WEEKDAY('Table'[Step Ended],2)
VAR _weekdayend=WEEKDAY('Table'[_next step start],2)
var _timediff1=DATEDIFF('Table'[_next step start],DATE(YEAR('Table'[_next step start]),MONTH('Table'[_next step start]),DAY('Table'[_next step start])+1),HOUR)-3
var _timediff2=DATEDIFF('Table'[Step Ended],DATE(YEAR('Table'[Step Ended]),MONTH('Table'[Step Ended]),DAY('Table'[Step Ended])+1),HOUR)-3
Return
IF((_weekdayend=6||_weekdayend=7),
IF(_weekdaystart=6||_weekdaystart=7,
_timediff1-_timediff2,
IF(_weekdaystart<>6||_weekdaystart<>7,
_timediff1,0)),
IF(_weekdaystart=6||_weekdaystart=7,
IF(_weekdayend<>6||_weekdayend<>7,
-_timediff2,0)))
Pick up Time (Step Ended - Step Started time on the next stage) =
var _weekdayend=WEEKDAY('Table'[Step Ended],2)
var _weekdaystart=WEEKDAY('Table'[_next step start],2)
var _datediff1=IF(ISBLANK('Table'[_next step start]),BLANK(),DATEDIFF('Table'[Step Ended],'Table'[_next step start],DAY))
var _datediff2=IF(ISBLANK('Table'[_next step start]),BLANK(),DIVIDE(DATEDIFF('Table'[Step Ended],'Table'[_next step start],MINUTE),60))
var _weekend=CALCULATE(COUNTROWS('Calendar table'),FILTER(ALL('Calendar table'),'Calendar table'[Date]>='Table'[Step Ended]&&'Calendar table'[Date]<='Table'[_next step start]&&'Calendar table'[is weekend]=1))+0
var _gap=_datediff1-_weekend
Return
IF(_gap<0,0,
_datediff2-_weekend*24-_gap*12+'Table'[Turnaround])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
First create a calendar table;
Then create 3 columns as below:
_next step start =
var _index=CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[Case Identifier]=EARLIER('Table'[Case Identifier])&&'Table'[Index]>EARLIER('Table'[Index])))
Return
CALCULATE(MAX('Table'[Step Started]),FILTER('Table','Table'[Index]=_index))
Turnaround =
VAR _weekdaystart=WEEKDAY('Table'[Step Ended],2)
VAR _weekdayend=WEEKDAY('Table'[_next step start],2)
var _timediff1=DATEDIFF('Table'[_next step start],DATE(YEAR('Table'[_next step start]),MONTH('Table'[_next step start]),DAY('Table'[_next step start])+1),HOUR)-3
var _timediff2=DATEDIFF('Table'[Step Ended],DATE(YEAR('Table'[Step Ended]),MONTH('Table'[Step Ended]),DAY('Table'[Step Ended])+1),HOUR)-3
Return
IF((_weekdayend=6||_weekdayend=7),
IF(_weekdaystart=6||_weekdaystart=7,
_timediff1-_timediff2,
IF(_weekdaystart<>6||_weekdaystart<>7,
_timediff1,0)),
IF(_weekdaystart=6||_weekdaystart=7,
IF(_weekdayend<>6||_weekdayend<>7,
-_timediff2,0)))
Pick up Time (Step Ended - Step Started time on the next stage) =
var _weekdayend=WEEKDAY('Table'[Step Ended],2)
var _weekdaystart=WEEKDAY('Table'[_next step start],2)
var _datediff1=IF(ISBLANK('Table'[_next step start]),BLANK(),DATEDIFF('Table'[Step Ended],'Table'[_next step start],DAY))
var _datediff2=IF(ISBLANK('Table'[_next step start]),BLANK(),DIVIDE(DATEDIFF('Table'[Step Ended],'Table'[_next step start],MINUTE),60))
var _weekend=CALCULATE(COUNTROWS('Calendar table'),FILTER(ALL('Calendar table'),'Calendar table'[Date]>='Table'[Step Ended]&&'Calendar table'[Date]<='Table'[_next step start]&&'Calendar table'[is weekend]=1))+0
var _gap=_datediff1-_weekend
Return
IF(_gap<0,0,
_datediff2-_weekend*24-_gap*12+'Table'[Turnaround])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi!
A lot of the latter questions are very trivial and you only need to research the very basics to achieve them. With bank holidays in Ireland I cannot help - maybe there's an online resource for those?
As for the main problem, you just need to add some simple Case Identifier iffing in a row by row method.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.