Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JonasPol
New Member

Calculate total hours of timeseries data by certain value

Hi,

 

I have a timeseries data table:

JonasPol_0-1607381069064.png

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

1 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

diff.png

 "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.

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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
Not applicable

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

Anonymous
Not applicable

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

diff.png

 "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.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors