Calculate total hours of timeseries data by certain value

[JonasPol]

Hi,

I have a timeseries data table:

I want to calculate the total time how long (in hours) a value was at the certain value.

So I want a result like:

• Mode 6 = 5,45h
• Mode 5 = 0,10h

How is it possible to calculated this in Power BI?

Kind regards

[Anonymous]

You could change the Time column to type number, and then Group By Mode, with sum for the aggregation for the now numeric time column. Then, all you have to do is change the type back to time.

--Nate

[Anonymous]

Actually, the more I read the question, the more I don't understand it!

[Anonymous]

Ok now I see what you mean. I'd still use Table.Group on the Mode Column, use a Min and a Max aggregation on Time, and then just subtract your new Min Time column from your Max Time column.

---Nate

[mahoneypat]

You should be able to use a measure like this in a table visual with your ModeName and/or ModeNbr columns.  Replace Table with your actual table name.

Hours Difference = DATEDIFF(MIN(Table[Time]), MAX(Table[Time]), HOUR)

Regards,

Pat

[JonasPol]

Hi Pat,

That's correct i know this function can give the difference between to data values.

Now the tricky part is I need to check the mode's number and if this is changed with previous value I can use the formula to calculated the time interval. This measurement should be stored in a seperated table so I if the it summurized for the mode number, it needs to be added.

The question is how to foresee this kind of calculations.

- Should I use a seperated table to calculated to total amount of hours for that mode?

  How can I use the datediff function in a IF function for the 'mode' value change?

Kind regards

[v-yingjl · Accepted solution]

Hi @JonasPol ,
"Should I use a seperated table to calculated to total amount of hours for that mode?"

If you want to calculated the hour total for each mode, the measure could be like this and show it in the table visual:

Measure = DATEDIFF(MIN('Table'[Time]),MAX('Table'[Time]),MINUTE) / 60

If you want to show the result for each mode in the whole table, the measure could be like this:

Diff = 
    DATEDIFF(
        CALCULATE(
            MIN('Table'[Time]),
            FILTER(
                ALL('Table'),
                'Table'[ModeNr] in DISTINCT('Table'[ModeNr])
            )
        ),
        CALCULATE(
            MAX('Table'[Time]),
            FILTER(
                ALL('Table'),
                'Table'[ModeNr] in DISTINCT('Table'[ModeNr])
            )
        ),MINUTE
    ) / 60

 "How can I use the datediff function in a IF function for the 'mode' value change?"

Not certain what is your expected. What does mode value change and use if function for it represent? My understanding is that you want to calculate the hour total for each mode as the above mentioned.

Attachend a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.