Forum Discussion
TAT Calculation based on Next Group
Hi,
I am struggling for some days to calculate the TAT for each case based on the actions done by each group and excluding weekends.
For example we have a case which was actioned by Group A, B, C & D and I want to find how long it took to be actioned by that group from start date to end date excluding weekends. It might be that the case was actioned by the same group twice. Each time the TAT is restarted when is actioned by another.
How can I calculate the TAT in Power BI. Do you think this is possible? I tried using RANKX and DATESBETWEEN but without success.
Below is an example on how my data looks like and how the TAT column should be at the end.
Case | Date | Group | TAT |
Case-1879 | 02/09/2017 01:13 | A | 0 |
Case-1879 | 02/09/2017 02:39 | A | |
Case-1879 | 02/09/2017 02:39 | A | |
Case-1879 | 02/09/2017 02:39 | B | 0 |
Case-1879 | 02/09/2017 02:39 | B | |
Case-1879 | 02/09/2017 02:39 | C | 1 Day |
Case-1879 | 02/09/2017 02:39 | C | |
Case-1879 | 02/09/2017 02:39 | C | |
Case-1879 | 04/09/2017 10:14 | C | |
Case-1879 | 05/09/2017 06:55 | B | 1 Day |
Case-1879 | 05/09/2017 06:55 | B | |
Case-1879 | 05/09/2017 09:55 | B | |
Case-1879 | 05/09/2017 09:55 | B | |
Case-1879 | 05/09/2017 15:13 | B | |
Case-1879 | 05/09/2017 15:14 | C | 4 Day |
Case-1879 | 09/09/2017 15:15 | C | |
Case-1879 | 09/09/2017 15:15 | D | 1 Day |
Case-1879 | 11/09/2017 18:31 | D | |
Case-1879 | 11/09/2017 18:31 | D |
Thank you and regards,
Sebas.
Hi Sebas7,
You can create a calendar table (Calendar = CALENDAR("2017-9-1",TODAY())) and add a index column in Query Editor,then create calculated columns below in the fact table:
WinStreak =
var LastLossIndex = CALCULATE( MAX('Table1'[Index]),
FILTER('Table1', 'Table1'[Group] <> EARLIER(Table1[Group])),
FILTER('Table1', [Index] <= EARLIER([Index]))
)return
[Index] - LastLossIndexNewGroup = CALCULATE(MAX(Table1[Index]),FILTER('Table1','Table1'[Index]<=EARLIER(Table1[Index]) && Table1[Group]=EARLIER(Table1[Group])&& Table1[WinStreak]=1&& Table1[Case]=EARLIER(Table1[Case])))
NewDate = DATEVALUE('Table1'[Date])
Diff = var MaxDate=CALCULATE(MAX('Table1'[NewDate]),FILTER('Table1','Table1'[Case]=EARLIER(Table1[Case])&&Table1[NewGroup]=EARLIER(Table1[NewGroup])))
var MinDate=CALCULATE(MIN('Table1'[NewDate]),FILTER('Table1','Table1'[Case]=EARLIER(Table1[Case])&&Table1[NewGroup]=EARLIER(Table1[NewGroup])))
return
IF(MinDate=MaxDate,0,DATEDIFF(LOOKUPVALUE('Calendar'[Date],'Calendar'[Date],MinDate),LOOKUPVALUE('Calendar'[Date],'Calendar'[Date],MaxDate),DAY)-CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar',('Calendar'[Weekday]=6 || 'Calendar'[Weekday]=7)&&'Calendar'[Date]>=MinDate&&'Calendar'[Date]<=MaxDate))+1)You can download attached pbix file to have a look.
Best Regards,
Qiuyun Yu
4 Replies
Hi,
I am struggling for some days to calculate the TAT for each case based on the actions done by each group and excluding weekends.
For example we have a case which was actioned by Group A, B, C & D and I want to find how long it took to be actioned by that group from start date to end date excluding weekends. It might be that the case was actioned by the same group twice. Each time the TAT is restarted when is actioned by another.
How can I calculate the TAT in Power BI. I tried using RANKX and DATESBETWEEN but without success.
Below is an example on how my data look like and how the TAT column should be at the end.
Case
Date
Group
TAT
Case-1879
02/09/2017 01:13
A
0
Case-1879
02/09/2017 02:39
A
Case-1879
02/09/2017 02:39
A
Case-1879
02/09/2017 02:39
B
0
Case-1879
02/09/2017 02:39
B
Case-1879
02/09/2017 02:39
C
1 Day
Case-1879
02/09/2017 02:39
C
Case-1879
02/09/2017 02:39
C
Case-1879
04/09/2017 10:14
C
Case-1879
05/09/2017 06:55
B
1 Day
Case-1879
05/09/2017 06:55
B
Case-1879
05/09/2017 09:55
B
Case-1879
05/09/2017 09:55
B
Case-1879
05/09/2017 15:13
B
Case-1879
09/09/2017 15:13
C
4 Day
Case-1879
09/09/2017 15:13
C
Case-1879
09/09/2017 15:13
D
1 Day
Case-1879
11/09/2017 18:31
D
Case-1879
11/09/2017 18:31
D
Thank you and regards,Sebastian.
Hi Sebas7,
Regarding your sample data, how did you calculate start date and end date then get the TAT column values? Did it calculate like below?
Best Regards,
Qiuyun YuHi Qiuyun,
Yes, that is how i calculated the TAT column.
Regards,
Sebas
Hi Sebas7,
You can create a calendar table (Calendar = CALENDAR("2017-9-1",TODAY())) and add a index column in Query Editor,then create calculated columns below in the fact table:
WinStreak =
var LastLossIndex = CALCULATE( MAX('Table1'[Index]),
FILTER('Table1', 'Table1'[Group] <> EARLIER(Table1[Group])),
FILTER('Table1', [Index] <= EARLIER([Index]))
)return
[Index] - LastLossIndexNewGroup = CALCULATE(MAX(Table1[Index]),FILTER('Table1','Table1'[Index]<=EARLIER(Table1[Index]) && Table1[Group]=EARLIER(Table1[Group])&& Table1[WinStreak]=1&& Table1[Case]=EARLIER(Table1[Case])))
NewDate = DATEVALUE('Table1'[Date])
Diff = var MaxDate=CALCULATE(MAX('Table1'[NewDate]),FILTER('Table1','Table1'[Case]=EARLIER(Table1[Case])&&Table1[NewGroup]=EARLIER(Table1[NewGroup])))
var MinDate=CALCULATE(MIN('Table1'[NewDate]),FILTER('Table1','Table1'[Case]=EARLIER(Table1[Case])&&Table1[NewGroup]=EARLIER(Table1[NewGroup])))
return
IF(MinDate=MaxDate,0,DATEDIFF(LOOKUPVALUE('Calendar'[Date],'Calendar'[Date],MinDate),LOOKUPVALUE('Calendar'[Date],'Calendar'[Date],MaxDate),DAY)-CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar',('Calendar'[Weekday]=6 || 'Calendar'[Weekday]=7)&&'Calendar'[Date]>=MinDate&&'Calendar'[Date]<=MaxDate))+1)You can download attached pbix file to have a look.
Best Regards,
Qiuyun Yu