Forum Discussion
Time comparison duration/difference
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
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]) ))
7 Replies
GeekAlfPro Just subtract them and then multiply by 24*60*60
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...
GeekAlfPro Right, I would use a custom format string instead.
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
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 !
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]) ))Hi,
Is it possible without actual and real?
