topic Re: Compare Dates in IF, and run DATEDIFF in formula in DAX Commands and Tips

Compare Dates in IF, and run DATEDIFF in formula

rocky_puff — Wed, 24 Mar 2021 01:09:14 GMT

Hi, im a new user in Power BI. Currently my task is to calculate the time difference between two dates, A and B, where supposed Date B is later than Date A. Since there are some technical error where Date A is later than Date B, it produces a negative result. Thus, now I need to do an IF to check if the Date A is later than Date B, return null, else run the calculation. But power BI is giving me an error.

Unless, I didn't run the calculation, and I return string type, then it would work fine. And please let me know if I declare null correctly.

Can anybody give some tips on the solution?

Re: Compare Dates in IF, and run DATEDIFF in formula

HotChilli — Wed, 24 Mar 2021 02:04:14 GMT

You have to return the same datatype from all paths of the 'if' statement. At the moment a string or a number are being returned. Try blank()

Re: Compare Dates in IF, and run DATEDIFF in formula

rocky_puff — Wed, 24 Mar 2021 02:32:35 GMT

But how to show the result of DATEDIFF calculation if I put blank() ?

Re: Compare Dates in IF, and run DATEDIFF in formula

HotChilli — Wed, 24 Mar 2021 02:35:56 GMT

I haven't looked at the logic of the calculation. I'm just trying to get you past the error. Replace "null" with blank()

Re: Compare Dates in IF, and run DATEDIFF in formula

Anonymous — Wed, 24 Mar 2021 05:22:55 GMT

IF you are creating the measure then use following DAX :

Measure =

VAR ABC = SELECTEDVALUE('Table'[Start])

VAR BC = SELECTEDVALUE('Table'[End])

RETURNIF(ABC<BC,DATEDIFF(ABC,BC,DAY),BLANK())

else in a calculated column :

Column = IF('Table'[Start]<'Table'[End],DATEDIFF('Table'[Start],'Table'[End],DAY),BLANK())

Re: Compare Dates in IF, and run DATEDIFF in formula

rocky_puff — Wed, 24 Mar 2021 05:49:39 GMT

Yes, I've tried like this before. But I had wrongly set the sequence and I didn't set the variable. Maybe that's why it run into error. But my solution is the same as this one, so I will mark this as a solution. Thanks!