Forum Discussion
TIME COMPARISON
Hi,
i want to calculate the difference between 2 hours (Theorical and real).
The trouble is that i have not the day, so i try to test if the hour is after 00:00, but i don't manage to solve my formula
i tried this post but without success
= Table.AddColumn(#"Type modifié2", "Ecarts", each if ( Time.Hour([Heure Théorique])>= Time(21,0,0) and Time([Heure Théorique])<= TIME(23,0,0) and Time.Hour([Heure réelle]) >= TIME(0,0,0) )
then [Heure réelle]-[Heure Théorique] +24
else [Heure réelle]-[Heure Théorique])
i tried also this
= Table.AddColumn(#"Type modifié2", "Ecarts", each if ( Time.Hour([Heure Théorique])>= 21 and Time.Hour([Heure Théorique])<= 23 and Time.Hour([Heure réelle]) >= 0 )
then [Heure réelle]-[Heure Théorique] +24
else [Heure réelle]-[Heure Théorique])
Thanks for any help
Wasn't able to find a solution using Power Query, but using DAX, I came up with this:
Ecarts = IF( [Heure reelle] - [Heure Theorique] > 0, [Heure reelle] - [Heure Theorique], TIME(24,0,0) - [Heure Theorique] + [Heure reelle] )Heure TheoriqueHeure reelleEcarts
23:57:20 00:07:20 23:50:00 00:01:10 23:59:10 23:58:00 22:11:05 22:11:35 00:00:30 If you really need to do this in PQ, perhaps you can try some similar logic.
Hope this helps.
Regards,
3 Replies
Wasn't able to find a solution using Power Query, but using DAX, I came up with this:
Ecarts = IF( [Heure reelle] - [Heure Theorique] > 0, [Heure reelle] - [Heure Theorique], TIME(24,0,0) - [Heure Theorique] + [Heure reelle] )Heure TheoriqueHeure reelleEcarts
23:57:20 00:07:20 23:50:00 00:01:10 23:59:10 23:58:00 22:11:05 22:11:35 00:00:30 If you really need to do this in PQ, perhaps you can try some similar logic.
Hope this helps.
Regards,
I will be on vacation next week. If you don't have any success, probably best to repost your question in a new thread, so you increase the chance of someone else helping you out.
