IF Statement based on before or after a set time of day

[abloor]

HI there,

Can I please get some help to create an IF statement that gives me one set of numbers for if it's before 2pm on Friday, and a different set of numbers if it's after 2pm?  I want to use UTCNOW for the my timezone (UTC +10) e.g.

UTCNOW()+(10/24)

If today is Friday AND the time is earlier than or equal to 2pm, then show me the [CurrentForecast} file.

If today is Friday AND the time is later than 2pm, then show me the [PreviousForecast} file.

Thanks very much!

[amitchandak · Accepted solution]

@abloor , try like

Switch(true()

weekday(today(),2) =5 && hour(now()) <14 , [[CurrentForecast],

weekday(today(),2) =5 && hour(now()) >14 , [[PreviousForecast],

[Forecast] // for else

)

[abloor]

Thank you @amitchandak  I have put that in.  Is there a way you can test time features like this in advance to see if it's working?  Or is it just waiting it out until Friday at 2pm and seeing if it changes?

[amitchandak · Accepted solution]

@abloor , try like

Switch(true()

weekday(today(),2) =5 && hour(now()) <14 , [[CurrentForecast],

weekday(today(),2) =5 && hour(now()) >14 , [[PreviousForecast],

[Forecast] // for else

)

[amitchandak]

@abloor , test it for Tuesday, weekday(today(),2) =2, if that works change back to 5

Switch(true()

weekday(today(),2) =5 && hour(now()) <14 , [[CurrentForecast],

weekday(today(),2) =5 && hour(now()) >14 , [[PreviousForecast],

[Forecast] // for else

)

[abloor]

Hi @amitchandak 

Thanks for your help so far.  I have this working partially, but I find it doesn't take effect until we are in the same day as UTC.  I am in UTC+10, so the DAX I'm using doesn't work until 10am, when UTC timezone has ticked over to the same day that I'm already in.  Can you please help adjust these so that if I were to need my DAX to reference 9am, it will work on the correct day?

I've tried two test as per the below, and both don't work until UTC timezone ticks over to match the day I'm in.

Test 1:

IF(WEEKDAY(UTCTODAY()+(10/24),2) = 3 && HOUR(UTCNOW()+(10/24)) < 9, [CurrentForecast],

IF(WEEKDAY(UTCTODAY()+(10/24),2) = 3 && HOUR(UTCNOW()+(10/24)) >= 9, [PreviousForecast],

 BLANK()

))

Test 2:

IF(WEEKDAY(TODAY(),2) = 3 && HOUR(UTCNOW()+(10/24)) < 9, [CurrentForecast],

IF(WEEKDAY(TODAY(),2) = 3 && HOUR(UTCNOW()+(10/24)) >= 9, [PreviousForecast],

BLANK()

))

Thank you

[v-jingzhang · Accepted solution]

Hi @abloor 

Both UTCTODAY and TODAY return a date with the time value 12:00:00 AM. They don't return an exact time value which you need to do the calculation in the WEEKDAY function. Instead, use UTCNOW() which returns the exact time and date.

IF (
    WEEKDAY ( UTCNOW () + ( 10 / 24 ), 2 ) = 3
        && HOUR ( UTCNOW () + ( 10 / 24 ) ) < 9,
    [CurrentForecast],
    IF (
        WEEKDAY ( UTCNOW () + ( 10 / 24 ), 2 ) = 3
            && HOUR ( UTCNOW () + ( 10 / 24 ) ) >= 9,
        [PreviousForecast],
        BLANK ()
    )
)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.