Forum Discussion

Milozebre's avatar
Milozebre
Helper V
8 years ago
Solved

Condition with Datediff

 

Hello,
I have a problem with datediff.
I have 2 columns with dates (date 1 and date 2) in the format dd / mm / yyyy HH: MM.
theoretically date 2 is always larger than date 1. But I have exceptions.
Datediff (Date 1, Date2, Hour) does not work because I have the case.
I would like to make a condition: if date1> date 2 then we will have the resutat "null" otherwise datediff (date1, date2, hour)

thx

Need Help
  • Milozebre's avatar
    Milozebre
    8 years ago

    Hi Communauty, 

     

    Thank you for your help. I tried for several days, different formulas but always the same results.

    Queries in SQL were working. So I left 1-2 days to think. And when I got back to this problem, I found that I put the formule in a calculated column.

    By doing a measure with a formula, it works.

     

    I'm sorry about the hassle :)

     

    Thanks again for the help.

11 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi Milozebre

     

    Its a bit ugly but you could try

     

    Column = IFERROR(DATEDIFF('Table2'[Column1],'Table2'[Column2],HOUR),BLANK())
  • Milozebre's avatar
    Milozebre
    Helper V
    8 years ago

    good morning Phil, 

     

    First thank you for your reply.

    I'm sorry but it doesnt work. 

    the result is null 

     

     

     

    • Milozebre's avatar
      Milozebre
      Helper V
      8 years ago

      Hi communauty, 

       

      i try several option woth if and iferror mais i have always same result.

       

      i need your help :)

       

      Thnak you in advance

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      If Phil's suggested formula does not work, then it means that entries in column 1 and 2 are text entries.  Convert them to actual dates and then the formula should work.

      • Milozebre's avatar
        Milozebre
        Helper V
        8 years ago

        Hi Ashish_Mathur, 

         

        I looked where the dates came from. I will be more specific. The two dates I am talking about are measures.
        These are dates I take and I added a filter:

         

        DatePriseEnCharge = CALCULATE(Min('Dossiers Traités/Actions'[DATE_ACTION]);'Dossiers Traités/Actions'[LibelleAction] = "Prise en compte par l'intervenant")

         

        DatePriseEnCharge = CALCULATE(Min('Dossiers Traités/Actions'[DATE_ACTION]);'Dossiers Traités/Actions'[LibelleAction] = "Prise en compte par l'intervenant")

         

        DelaiPriseencharge = DATEDIFF([DateActionMinAffectation];[DatePriseEnCharge];HOUR)

         

         

        I hope this will help :)

         

        Sorry if I misled you

         

         

  • Milozebre's avatar
    Milozebre
    Helper V
    8 years ago

    Hi Communauty, 

     

    Thank you for your help. I tried for several days, different formulas but always the same results.

    Queries in SQL were working. So I left 1-2 days to think. And when I got back to this problem, I found that I put the formule in a calculated column.

    By doing a measure with a formula, it works.

     

    I'm sorry about the hassle :)

     

    Thanks again for the help.