TAT Calculation based on Next Group

[Sebas7]

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.

[v-qiuyu-msft]

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 Yu 

[Sebas7]

Hi Qiuyun,

Yes, that is how i calculated the TAT column.

Regards,

Sebas

[v-qiuyu-msft · Accepted solution]

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] - LastLossIndex

NewGroup = 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 

[Sebas7]

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.