Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
)
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.
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?
@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 , 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
)
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |