DAX experts, I need help with time manipulation

[rogletree]

I have a table that tracks downtimes for machines. Some fields I have are [start_time] & [end_time] (both are datetime), [duration] (in minutes as a decimal number). Those are all pretty self-explanatory. I have created another field in power query called [date_hour] (as datetime) that is the hour of the day that a downtime occurred, for example if a downtime started at 10:15 am on October 13th, it would show as "10/13/2020 10:00:00 AM".

Some of these downtimes are only a few minutes, but some of them can last several hours. I want to display these visuals as graphs where I can drill down to the [date_hour] field for specific machines and see how many minutes they were down for any given hour of the day. But with how I currently have it modeled, if a downtime starts at 10:15 AM and lasts for 120 minutes, it shows that the 10 AM hour has 120 minutes of downtime, and obviously there can only be 60 minutes in an hour so it showing 120 minutes of downtime in one hour is kind of misleading.

So basically what I would like to do is, if there is a downtime that is longer than 60 minutes, have a DAX expression that basically drags it out across however many [date_hour] fields are necessary for however many minutes it is.

Is this something that sounds feasibly? I'm not that good at DAX so I figure there are some people around here that are really good at it that might have some ideas.

[Daviejoe]

Calling @Greg_Deckler 

Hope you didn't mind me tagging you in this one!  Reminded me of your MTBF chapter.

[AlB]

Hi @rogletree 

I haven't understood. Can you show an example based on data with the expected result? To help clarify a bit

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[rogletree]

@AlB sure, here are a couple screenshots.

Here is a row from my table. As you can see, for the "duration" it shows 375.262. This is in minutes, so that comes out to a little over six hours, as shown by the start time and end time. And since the start time was at 4:06AM, the "date_hour" is at 4AM for that date.

Now when plotted on a graph, this is how it looks. This view shows all 24 hours of one date for one particular machine. That one long bar represents the 375 minute downtime event but it shows it as being within the 4AM hour, which is exactly how I told Power BI. You can also see that for the 5AM, 6AM, 7AM, 8AM, and 9AM hours it shows no downtimes, when in reality the 375 minute downtime carried over throughout those hours. Then in the 10AM hour that small sliver of a bar is for a downtime event that was a little over one minute, not related to the big one.

So I'm hoping that there is a way to kind of "spread out" the downtimes that are longer than 60 minutes, or if it goes from one hour into another hour, to show that. Because right now the visual looks misleading with how I have it.

[AlB · Accepted solution]

@rogletree 

1. You need a complete date-time table with all the hours, no gaps. You should also have a full date table with all days in the year (full years) to avoid unexpected issues.

2. No relationship between date-time table and Main table 

3. Create this measure for the chart:

Measure = 
VAR startSlot_ = SELECTEDVALUE('date hours'[date_hour])
VAR endSlot_ = startSlot_ + (1/24) //1 hour later
RETURN
SUMX(Main, 
VAR aux_ =  MIN(endSlot_,Main[end_time])- MAX(startSlot_, Main[start_time])
VAR timeInSlot_ = IF(aux_>=0, 24*60*aux_, 0)
RETURN
timeInSlot_)

4. See it all at play in the attached file

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[rogletree]

@AlB thank you so much!