TIME COMPARISON

[GeekAlfPro]

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

[rsbin · Accepted solution]

@GeekAlfPro ,

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,

[GeekAlfPro]

hi @rsbin 

thanks for your time.

i will try next week and will keep you posted

[rsbin]

@GeekAlfPro ,

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.