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.

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

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

    Hi Angélia, 

     

    Yes, i have make a mistake with the measures : 

     

    DateActionMinAffectation = CALCULATE(Min('Dossiers Traités/Actions'[DATE_ACTION]);'Dossiers Traités/Actions'[LibelleAction] = "Affectation à l'équipe")

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

     

    You can find in attachment the result... all rows are blank 

     

     

     

     

     

     

     

     

     

     

     

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

      Hi Milozebre,

      The difference between the dates are tiny based on your screenshot. Please change the HOUR to SECOND in the measure. And check the results.

      Best Regards,
      Angelia