Forum Discussion

Sebas7's avatar
Sebas7
Regular Visitor
8 years ago
Solved

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, ...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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 

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

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's avatar
Sebas7
Regular Visitor
8 years ago

Hi Qiuyun,

 

Yes, that is how i calculated the TAT column.

 

Regards,

Sebas

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    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