IF statement between 2 times

[Anonymous]

hi all,

i have been tasked to provide a hour by hour report so throughout the day we can see how many hours are available in our factory.

If i run the below query i can see that if the UTC time is between 8:30 and 11:30 then i need to multiply the available employees by 3.

if(right(UTCNOW(),8)>Time(8,30,00)<Time(11,30,00),SUM(Query1[Availability])*3,0)

However when i run it i get the below error, its something to do with the SUM(Query1[Availability])*3,0 but i can not identify what.

DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

Any help much appreciated

thanks

[Anonymous]

Hi @Anonymous 

this problem occurs because after applying left operator, your field is converted to string, so I recommend you use this expression:

IF(AND(DATE(RIGHT(LEFT(UTCNOW(),10),4),LEFT(UTCNOW(),2),RIGHT(LEFT(UTCNOW(),5),2))>Time(8,30,00),DATE(RIGHT(LEFT(UTCNOW(),10),4),LEFT(UTCNOW(),2),RIGHT(LEFT(UTCNOW(),5),2))<Time(11,30,00)),SUM(Query1[Availability])*3,0)

[Anonymous]

Hi @Anonymous 

Thank you, i get no errors but the expression doesn't return any results.

i thought it might be the times so i changed the times to be between 0,0,01 to 23,59,0 and to return a 1 but i still get 0. If i can get this part working then i will work on the actual calculation i need to put in after

Regards

Paul