Time comparison duration/difference

[GeekAlfPro]

Hi,

I have a table with columns of hours

i have a theorical hour and a real hour, and I want to have the advances and delays

In Dax, how can I get the differences lower than 0?

i tried this code, but without success

heure colonne = switch(TRUE(),
	and(
		and(HOUR('Table Tests Horaires'[Heure Théorique])>=21, HOUR('Table Tests Horaires'[Heure Théorique])<=23),
    HOUR('Table Tests Horaires'[Heure réelle])>=0),
    'Table Tests Horaires'[Heure réelle]-'Table Tests Horaires'[Heure Théorique]+24,
    
	and(
	and(
		HOUR('Table Tests Horaires'[Heure réelle])<=23, HOUR('Table Tests Horaires'[Heure réelle])>=21),
    HOUR('Table Tests Horaires'[Heure Théorique]>=0)),
    'Table Tests Horaires'[Heure réelle]-('Table Tests Horaires'[Heure Théorique]-24),	
	
	'Table Tests Horaires'[Heure réelle]-'Table Tests Horaires'[Heure Théorique]-24
    )

The column hour column gives me positive time but not differences

Thanks for your help

[Anonymous]

Hi  @GeekAlfPro ,

Here are the steps you can follow：

1. Create calculated column.

Column = IF( HOUR('Test'[actual]) >=21 && HOUR('Test'[real]) <=3 , [real]+24-[actual] ,  IF( HOUR('Test'[actual])<=3 && HOUR('Test'[real]) >=21 , [real]-24-[actual] , [real]-[actual]))

2. Result:

If you need pbix, please click here.

Time comparison durationdifference.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Karthik12]

Hi,

Is it possible without actual and real?

https://community.powerbi.com/t5/Desktop/Time-difference-between-current-output-and-previous-output/...

[GeekAlfPro · Accepted solution]

Hi,

i finally manage to get the result i want.

i set 2 more columns, based on the decimal result.

if it's <0 it's an advance

if it's >0 it's delayed

the final code is : 

Colonne Ecarts = IF(
    HOUR(Journal[Heure théo])>= 21 && HOUR(Journal[Heure théo])<= 23 && HOUR(Journal[Heure Départ Réelle])>=0 && HOUR(Journal[Heure Départ Réelle])<=3,
     (Journal[Heure Départ Réelle]+1-Journal[Heure théo]), 
     IF(HOUR(Journal[Heure Départ Réelle])<=23 && HOUR(Journal[Heure Départ Réelle])>=21 && HOUR(Journal[Heure théo])>=0 && HOUR(Journal[Heure théo])<=3,
      (Journal[Heure Départ Réelle]-1-Journal[Heure théo]),
      (Journal[Heure Départ Réelle]-Journal[Heure théo])
    ))

[GeekAlfPro]

Hi @Anonymous 

Thanks for your response, your code is better than mine !

Nevertheless, in the second row, i expect to have a negative différence because the real is before the actual.

in your suggestion, i see only a positive difference, how can i have the negative difference ?

Another Question : what is the unit of the result of the calculated column ?

Thanks again !

[Greg_Deckler]

@GeekAlfPro Just subtract them and then multiply by 24*60*60

[GeekAlfPro]

Hello @Greg_Deckler 

Thanks for your proposal, but when i test, if the difference is negative, i have no data once i formated the column in hour but maybe as it's an hour format, it can't be negative...

[Greg_Deckler]

@GeekAlfPro Right, I would use a custom format string instead.