Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

SLA TIME

Hello Guys! I need a help!


I need to know how much time was spent, to finalize an incident. 

But this incident can contain more than 2 steps, like this


I have one step with start in 10/10/2018 10:00:00 and end 10/10/2018 11:00:00 - This is 1 hour.

I have other step with start in 10/10/2018 10:30:00 and end 10/10/2018 11:30:0 - This is 1 hour too, but it starts in the range of first step and end 30 minutes after first step, So this step as used 30 minutes of incident SLA

And I have the last step with start in 10/10/2018 14:00:00 and end 10/10/2018 15:00:00 - This is 1 hour and it is not in a range of two other steps, so 1 Hour! 

At end I have 2 hours and 30 minutes used by this incident. Like this:

How can I calculate this?

Thanks in advance and sorry my bad english!

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    Suppose Start in sorted in ascend order, you may create a calculate column using DAX below:

    SLA Time Each Step = 
VAR Current_Step = 'Table'[STEP]
VAR Current_Step_Start = CALCULATE(MAX('Table'[Start]), FILTER('Table', 'Table'[STEP] = Current_Step))
VAR Current_Step_End = CALCULATE(MAX('Table'[End]), FILTER('Table', 'Table'[STEP] = Current_Step))
VAR Previous_Step_Start = CALCULATE(MAX('Table'[Start]), FILTER('Table', 'Table'[STEP] = Current_Step - 1))
VAR Previous_Step_End = CALCULATE(MAX('Table'[End]), FILTER('Table', 'Table'[STEP] = Current_Step - 1))
RETURN
IF(Current_Step_Start < Previous_Step_End, IF(Current_Step_End > Previous_Step_End, DATEDIFF(Previous_Step_End, Current_Step_End, MINUTE), 0), DATEDIFF(Current_Step_Start, Current_Step_End, MINUTE))

    Then create a measure using DAX like this:

    SLA Time = 
VAR Total = SUM('Table'[SLA Time Each Step])
VAR Hour = INT(Total / 60)
VAR Minute = Total - Hour * 60
RETURN
Hour & "Hour" & Minute & "Minute"

     

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Anonymous ,

     

    Suppose Start in sorted in ascend order, you may create a calculate column using DAX below:

    SLA Time Each Step = 
VAR Current_Step = 'Table'[STEP]
VAR Current_Step_Start = CALCULATE(MAX('Table'[Start]), FILTER('Table', 'Table'[STEP] = Current_Step))
VAR Current_Step_End = CALCULATE(MAX('Table'[End]), FILTER('Table', 'Table'[STEP] = Current_Step))
VAR Previous_Step_Start = CALCULATE(MAX('Table'[Start]), FILTER('Table', 'Table'[STEP] = Current_Step - 1))
VAR Previous_Step_End = CALCULATE(MAX('Table'[End]), FILTER('Table', 'Table'[STEP] = Current_Step - 1))
RETURN
IF(Current_Step_Start < Previous_Step_End, IF(Current_Step_End > Previous_Step_End, DATEDIFF(Previous_Step_End, Current_Step_End, MINUTE), 0), DATEDIFF(Current_Step_Start, Current_Step_End, MINUTE))

    Then create a measure using DAX like this:

    SLA Time = 
VAR Total = SUM('Table'[SLA Time Each Step])
VAR Hour = INT(Total / 60)
VAR Minute = Total - Hour * 60
RETURN
Hour & "Hour" & Minute & "Minute"

     

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.