Forum Discussion

GeekAlfPro's avatar
GeekAlfPro
Helper V
3 years ago
Solved

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 co...
Need Help
  • GeekAlfPro's avatar
    GeekAlfPro
    3 years ago

    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])
    ))
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • GeekAlfPro's avatar
    GeekAlfPro
    Helper V
    3 years ago

    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 !

  • GeekAlfPro's avatar
    GeekAlfPro
    Helper V
    3 years ago

    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])
    ))