topic Re: if statement time based in DAX Commands and Tips

if statement time based

AI14 — Mon, 20 Feb 2023 14:33:20 GMT

Is there a formula to compare 2 times if less than or more than a fixed time for example,

Actual 09:59

Target 10:00

if Actual is less than Target by 10 mins then 1 if not 0

tamerj1 — Tue, 21 Feb 2023 02:23:10 GMT

IF ( DATEDIFF ( 'Table[Actual], 'Table[Target], MINUTE ) = 10, 1, 0 )

AI14 — Mon, 20 Feb 2023 15:26:50 GMT

Thanks,

Would i need to add a < to say if its less than?

tamerj1 — Mon, 20 Feb 2023 16:55:59 GMT

I believe it should be difference > 10

BrianConnelly — Mon, 20 Feb 2023 19:23:54 GMT

For this we will convert your time to DateTime using the TimeValue and then calculate minutes using DateDiff.

DATEDIFF (TIMEVALUE(time_text) ,TIMEVALUE(time_text) ,MINUTE)

Then build your switch statement or if statement for the logic. Many ways to do this, but here are a few...

SWITCH(TRUE() ,DATEDIFF (TIMEVALUE(time_text) ,TIMEVALUE(time_text) ,MINUTE) > 10, 1 ,0)

IF(DATEDIFF (TIMEVALUE(time_text) ,TIMEVALUE(time_text) ,MINUTE)>10,1,0)