Condition with Datediff

[Milozebre]

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

[Milozebre · Accepted solution]

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]

good morning Phil, 

First thank you for your reply.

I'm sorry but it doesnt work. 

the result is null 

[Ashish_Mathur]

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]

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]

Hi,

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

[Milozebre]

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]

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]

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]

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

[Milozebre]

Hi communauty, 

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

i need your help 🙂

Thnak you in advance

[Phil_Seamark]

Hi @Milozebre

Its a bit ugly but you could try

Column = IFERROR(DATEDIFF('Table2'[Column1],'Table2'[Column2],HOUR),BLANK())