Calculate Total Hours and Minutes (for every hour) and total days

[JulianaMacedo]

Hi!

I need help.
 I have a table with a time stamp as the example below. I have tried using this time stamp directly or creating a time table, either way I can't figure out what is wrong.

I need to calculate the total of days, hours and minutes from this by device / state. The thing is... I'm trying to calculate for how long time a device stays in a specific state (which basically means someone pressed a button, then I need the total of time out of it). The hours are not continous though, a certain device can be in a determined state from 12:00 -14:00 for example, and then start again at 16:00-17:00. Because of that, I need to calculate first the total of minutes and seconds per hour, and then the total hours per day, and total days per month.

So the outcome I'm looking for is a measure that will show a result like this:
Filter on ID 24 for month X:        5d 12h 30min

I have tried different calculates such as:

Total Time = 
CALCULATE(
    1.0*(MAX(production[Time]) - MIN(production[Time])),
    FILTER(
        production,
        Hour(production[Time])
    ))

And:

TEST = MINX(FILTER(TimeDim, TimeDim[Hour] <=23 || TimeDim[Hour] >22 ), TimeDim[Minute] + (TimeDim[Second]/60))

But none of them yields the results I need.

Example of data:

LogData ID       Date                     Time           Hour    Minute Second

1162	23	2022-06-09	11:21:01	11	21	1
1162	225	2022-06-09	11:21:01	11	21	1
1163	225	2022-06-09	11:21:01	11	21	1
1164	8	2022-06-09	11:21:04	11	21	4
1164	225	2022-06-09	11:21:04	11	21	4
1165	8	2022-06-09	11:21:04	11	21	4
1165	24	2022-06-09	11:21:04	11	21	4
1165	225	2022-06-09	11:21:04	11	21	4
1166	24	2022-06-09	11:21:04	11	21	4
1166	225	2022-06-09	11:21:04	11	21	4
1167	8	2022-06-09	11:21:04	11	21	4
1167	24	2022-06-09	11:21:04	11	21	4
1167	225	2022-06-09	11:21:04	11	21	4
1168	8	2022-06-09	14:21:05	14	21	5
1168	225	2022-06-09	14:21:05	14	21	5
1169	225	2022-06-09	14:21:05	14	21	5
1170	24	2022-06-09	14:21:05	14	21	5
1170	31	2022-06-09	14:21:05	14	21	5

I appreciate any help

[v-shex-msft]

Hi @JulianaMacedo,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[JulianaMacedo · Accepted solution]

I finally found a solution, following this post:

https://community.powerbi.com/t5/Desktop/Time-in-State/m-p/941429

In case anyone has the same problem.

[daXtreme]

Hi @JulianaMacedo 

Just wondering... Why don't you calculate this in Power Query or in the source system? Would that not be so much easier? You could calculate this in Power Query with ease since PQ has all the needed functions to extract the pieces of info you need from a period of time. It's doable in DAX as well but with much, much more effort.

You could pre-calculate the number of minutes (as this is you base granularity) between each two consecutive dates for any one ID and place them in a table from which you'd only need to read them out and sum up if desired. Then, once you've got the total number of minutes, it'd be easy to retrieve the number of days, hours and remaining minutes... right?

[JulianaMacedo]

@daXtreme 

Hi, it is actually a good idea... it was what I was thinking during the weekend that it would be better maybe to aggregate it before and make an aggregated table out of it before moving to DAX.
But I'm still not sure if PQ has what I need or it will be just as much work as in DAX, maybe just using SUMMARIZE function in DAX might do it.

I'll have to test a bit to find a solution, thanks for the idea.

[daXtreme]

Hi again

If you need to pre-calculate something, Power Query is the way to go. ALWAYS. This component of PBI is more than capable of doing any calculation (even recursive, where DAX is useless)... and with ease that you very often won't find in DAX.