Calculate ratio of sum of each status duration Vs. planned operational time duration

[Anonymous]

Hello,

Currently I am having trouble with calculating the ratio between the time duration of statusses of a machine and the planned operational time duration.

The table PlannedOperationalActivity contains the planned start and end time of the machine.

PlannedOperationalActivityID	DeviceID	MondayStartDateTime	MondayEndDateTime	TuesdayStartDateTime	TuesdayEndDateTime	WednesdayStartDateTime	WednesdayEndDateTime	ThursdayStartDateTime	ThursdayEndDateTime	FridayStartDateTime	FridayEndDateTime
1	1	09:00:00	18:00:00	09:00:00	18:00:00	09:00:00	18:00:00	09:00:00	18:00:00	09:00:00	18:00:00
2	2	09:00:00	18:00:00	09:00:00	18:00:00	08:30:00	17:30:00	09:00:00	18:00:00	09:00:00	18:00:00
3	3	09:00:00	18:00:00	09:00:00	13:00:00	09:00:00	18:00:00	09:00:00	13:00:00	09:00:00	13:00:00

The table DeviceOperationalState contains which status has occured and how long the status was active.

DeviceOperationalStateID	DeviceID	OperationalStateID	StateDateTimeStart	StateDateTimeEnd
1	1	7	02/01/2021 08:00	02/01/2021 09:00
2	1	4	02/01/2021 09:00	02/01/2021 11:14
3	1	3	02/01/2021 11:14	02/01/2021 14:05
4	1	5	02/01/2021 14:05	02/01/2021 14:10
5	1	4	02/01/2021 14:10	02/01/2021 18:00
6	1	8	02/01/2021 18:00	02/01/2021 18:31
7	3	7	21/01/2021 08:00	21/01/2021 09:00
8	3	4	21/01/2021 09:00	21/01/2021 18:00
9	3	8	21/01/2021 18:00	21/01/2021 19:00

What I want to calculate is the sum of time for each status of a DeviceID for one day and devide this by the duration between the corresponding weekday planned time duration.

An example is 21/01/2021 of DeviceID 3, which is a thursday. The sum of the status durations is 60+540+60=660mins

The planned operational activity is ThursdayEndDateTime-ThursdayStartDateTime = 18:00:00-09:00:00 = 09:00:00 = 540mins.

The ratio is than 660/540*100 = 122%. StatusID 7 = 11%, StatusID 4=100% and StatusID 8 = 11%. 

In a chart I want to show per day of the month that the machine was active what the ratio was of planned operational time Vs. the actual time in which the total real active time bar is split in segments per status. 

Help is greatly appreciated.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Click Transform data to enter Power query, select [DeviceID], click Transform – Unpivot Columns – Unpivot Other Columns

Result:

2. Create calculated column.

datediff = DATEDIFF([StateDateTimeStart],[StateDateTimeEnd],MINUTE)

Result:

3. Create calculated table.

Summarize =
SUMMARIZE('Table (2)',[DeviceID],"Time duration",SUM('Table (2)'[datediff]),"Weekday",FORMAT(WEEKDAY(MAX('Table (2)'[StateDateTimeStart]),1),"dddd"))

Result:

4. Create calculated column.

Percent1 =
var _total=
SUMX(FILTER(ALL('Table (2)'),[DeviceID]=EARLIER('Table (2)'[DeviceID])),[datediff])
var _percent=
DIVIDE([datediff],_total)
var _related=
CALCULATE(MAX('Summarize'[Percent]),FILTER(ALL('Summarize'),[DeviceID]=EARLIER('Table (2)'[DeviceID])))
return
_related*_percent

5. Result：

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.

[HarishKM]

@Anonymous  create a cal. col.
time diff = DATEDIFF(Sheet1[Start time],Sheet1[End time],MINUTE)
Then create 1 more measure for your operational time as well .
then divide update the format.

[Anonymous]

Hello @HarishKM, 

Thank you for responding.

Whilst following your instructions I have become stuck once again.

Your solution is creating a measure for operational time, assuming you mean with the same formula (

TimeDiffOperationalState = DATEDIFF(DeviceOperationalState[StateDateTimeStart],DeviceOperationalState[StateDateTimeEnd],MINUTE)). This formula is not possible because the measure does not accept anything but a measure. I created another calculated column instead with the same formula.

When creating a measure to divide both time durations, both calculated columns are not selectable. 

RatioTimeDurations = DIVIDE(PlannedOperationalActivity[ThursdayTimeDiffMin],DeviceOperationalState[TimeDiffOperationalStateColumn])

Also, this formula would only take in consideration the time difference if the weekday is Thursday. If the date is 22nd of Januari 2021, the formula should use friday Start and End as ratio and if it is the 20th of Januari 2021 the formula should use the time difference of Wednesday as ratio.

I try to attach the PBI file, but I'm not allowed to.