Forum Discussion
How do i compare time? power BI desktop
So i have a time column as hh:mm:ss am. i want to compare this time column with static time values ( shift timings) and to add shifts to my data. M code or Dax funtion works. i cannot figure out how to actually compare time in IF statements.
One other trick I've used many times is relying on the inherient 'math' of Power BI's native database to my advantage...
Here's an exmaple of a Table with Time and Duration
Since the fundamental staorage of this data is numeric, you can perform simple math functions against them to get end results...
Mabye some of your compares can be broken down into > or < simple queries?
Hope this helps...
Forrest
Good catch fhill , I was thinking duration but it looks like those are actual Times so fundamentally they are stored as decimal numbers with the decimal portion being the fractional day so each hour is stored as 1/24, minute 1/60 and second 1/360.
3 Replies
- Greg_Deckler
Community Champion
NaddingGod - You will need to convert those to seconds, add them and essentially convert them back more or less. Look at the links below, they should get you what you need.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92 - fhill
Resident Rockstar
One other trick I've used many times is relying on the inherient 'math' of Power BI's native database to my advantage...
Here's an exmaple of a Table with Time and Duration
Since the fundamental staorage of this data is numeric, you can perform simple math functions against them to get end results...
Mabye some of your compares can be broken down into > or < simple queries?
Hope this helps...
Forrest
- Greg_Deckler
Good catch fhill , I was thinking duration but it looks like those are actual Times so fundamentally they are stored as decimal numbers with the decimal portion being the fractional day so each hour is stored as 1/24, minute 1/60 and second 1/360.