Forum Discussion

Milozebre's avatar
Milozebre
Icon for Helper V rankHelper 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 excepti...
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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper 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
Icon for Helper V rankHelper 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

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    I do not see a mistake in any of those formulas.  I will need to see the file.

    • Milozebre's avatar
      Milozebre
      Icon for Helper V rankHelper V
      8 years ago

      Hi, 

       

      The queries work but when I do a simple datediff, I have a message that tells me I have a circular data error.
      I would like to find the time difference between the 2 measures  "date affectation"  and the "date de prise en charge" by adding a condition.
      If the "date de prise en charge" is smaller than the "date affection" then I want to see "error" otherwise make DATEDIFFF (dateaffectation; dateprisencharge; hour)
      And for the file, it is so huge and in figure so-called private data...

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Milozebre,

        Please try to use the following formula and check if it works fine.

        result=IF([date de prise en charge]<[date de prise en charge],blank(),DATEDIFFF([dateaffectation],[dateprisencharge],hour))


        In your post above, the two formulas for creating measure for date are same?

        Best Regards,
        Angelia