Calculate Uptime/Down time with multiple overlapping periods

[Anonymous]

I have been struggling to find the solution for this calculation.

I have data something like this 

And my output and expected output is 

If I don't select anything from Type slicer it should return all the dates with Downtime which should not be morethan 24 hours in expected output but I am getting 

Is there any solution for this with Measures? So I can able to slice my data and get th accurate values in visuals.

 Here is sample .pbix file.  https://drive.google.com/file/d/19XcxqyJcBxQy1mZSop4_cF1iEEGm61pJ/view?usp=sharing

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

This is the overall process:

Here are the steps you can follow：

1. Create Calendar

Date = CALENDARAUTO()

2. Create calculated column.

Date1 = [Date]-0

3. Create measure.

Flag =
var _min=MINX(ALLSELECTED(UpTime),[Start])
var _max=MAXX(ALLSELECTED(UpTime),[End])
return
IF(MAX('Date'[Date1])>=_min-1&&MAX('Date'[Date1])<=_max,1,0)

4. Place it in Filter, set is=1, applyfilter

5. Create measure.

DownTime (hrs2) =
var _date=CALCULATE(MAX('Date'[Date1]),FILTER(ALL('Date'),[Date1]=DATE(YEAR(MAX([End])),MONTH(MAX([End])),DAY(MAX([End])))))
var _datetable=UNION(SELECTCOLUMNS('UpTime',"1",[Start]),SELECTCOLUMNS('UpTime',"1",[End]))
var _currentdate=MAXX(FILTER(_datetable,YEAR([1])=YEAR(MAX('Date'[Date1]))&&MONTH([1])=MONTH(MAX('Date'[Date1]))&&DAY([1])=DAY(MAX('Date'[Date1]))),[1])
return
ABS(DATEDIFF(_date,_currentdate,SECOND)/3600)

6. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create Calendar

Date = CALENDARAUTO()

2. Create calculated column.

Date1 = [Date]-0

3. Create measure.

Flag =
var _min=MINX(ALLSELECTED(UpTime),[Start])
var _max=MAXX(ALLSELECTED(UpTime),[End])
return
IF(MAX('Date'[Date1])>=_min-1&&MAX('Date'[Date1])<=_max,1,0)

4. Place it in Filter, set is=1, applyfilter

5. Result:

You can downloaded PBIX file from here.

In your data, 2020.8.25 is 20.1 and 2020.8.26 is 23.5. How is this calculated?

Best Regards,

Liu Yang

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

[Anonymous]

Hi @v-yangliu-msft  Thanks for your time.

I have only one record for type 0. Tis Down time started on 8/25/2020 3:50:02 AM and Ended on 8/26/2020 11:26:51 PM. My total down time for this type 0 record is 43:61 hours. But when I am representing it by date wise this should be split in to 20:1 hr and 23:5 on 8/25/2020 & 8/26/2020 respectively. Actually this is my expected out put. 

I have more records in the type slicers not only 0 and 1. So my expected output should be change as per my type selection which is similar to my above explanation.

I've tried those steps which you provided above but not giving me the expected output. Can you provide me some solution which should give the Expected Output.