Correctly allocate time difference between events to individual days.

[zippelm]

Hi Guys, 

i have a little trouble calculating ratios between diffrent machine availibilty events. 

Given is a table, which represents a protocol for state events of several machines. The machine, the state and the timestamp are given in each case. The state is also valid over days.

The goal is to calculate and display the ratios of the states up, down and idle per machine and related to days, weeks and months.

In the first approach I created a query which filters on a machine and assigns an index. Then I used the start time of the following event as the end time of the previous one. However, the time span to be calculated from this mostly contains time components of the following day and therefore cannot be put into a correct relation for a 24 hour day.

I would like to create a measure for each of the states, which realizes the described behavior using Time intelligence functions of Power BI. I could then set the filter for the individual machines in the measure or in the visual. Unfortunately, I do not have the correct approach. Maybe one of you Superusers can help me 🙂 

Machine	State	Timestamp
A	down	10.01.2023 06:00
A	idle	10.01.2023 07:00
C	up	10.01.2023 07:00
A	up	10.01.2023 10:00
B	up	10.01.2023 23:00
B	idle	11.01.2023 02:00
C	down	11.01.2023 04:00
A	up	12.01.2023 10:00
B	idle	12.01.2023 18:00
A	idle	13.01.2023 13:00
C	up	13.01.2023 23:00
C	idle	14.01.2023 06:00
C	ilde	14.01.2023 11:00
C	down	14.01.2023 16:00
B	down	14.01.2023 19:00

Screenshot shows the problem: An state starts on 30.09.2022 and is valid for 93 hours. These hours are only attributed to this single day