Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
How is it possible to calculated this in Power BI?
Kind regards
Solved! Go to 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.
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
Actually, the more I read the question, the more I don't understand it!
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
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
To learn more about Power BI, follow me on Twitter or subscribe 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
"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.
Check out the July 2025 Power BI update to learn about new features.