Forum Discussion
PowerBI Service Timezone incorrect when checking if date is in current week
Hi,
I am using a query to create a table that has a list of dates and a field called "isCurrentWeek" that flags whether or not the date is in current week or not using Date.IsInCurrentWeek.
I am finding that Date.IsInCurrentWeek is using PowerBI Timezone UTC rather than my local timezone when testing if the date is in Current week or not (PowerBI Service Auto refresh).
I am either 10-11 hours (could change based on daylight savings) ahead of UTC. This means that on a Monday morning, when the reports auto refresh, I have to wait until 10-11am until the current week switches to the correct current week.
Is there anyway to do this true/false isincurrentweek test using PowerBI service but using my local time zone rather than the PowerBI Service timezone
Please see below query:
let
Source = Query1(#date(2016, 1, 1), 5000, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "yearWeek", each Date.Year([Date])*100+Date.WeekOfYear([Date])),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"yearWeek"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "WeekCounter"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "IsCurrentWeek", each Date.IsInCurrentWeek([Date]))
in
#"Added Custom1"
Thanks
8 Replies
Hi threw001 ,
Regarding your question, UTC time zone is used by default in Power BI Service and cannot be changed. My idea is to additionally create a new column with the time offset. Use the 'IsInCurrentWeek' function for this column.
Something like this.(Assume my time difference from UTC is 10 hours)
Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Date] - #duration(0,10,0,0) ) in #"Added Custom"
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous
I am afraid no luck. I am getting incorrect flag as per below screenshot. It is pulling +1 week as current week.
Any suggestions?
Thanks
Hi threw001 ,
Let's reorganize our thinking.
Assuming the data in column 'Date' is 7/1/2024 1:00:00AM
Assuming the current local time is 7/1/2024 2:00:00AM
UTC time in Power BI Service is 6/30/2024 4:00:00PM (10 hours)
1. Add a new column 'Column1' to the Power Query and use the 'IsInCurrentWeek' function on the column 'Date'. (The result is true)
2. Add a new column 'UTC Date based on the time offset. (7/1/2024 1:00:00AM - 10 hours = 6/30/2024 3:00:00AM)
3. Add a new column 'Column2' to the Power Query and use the 'IsInCurrentWeek' function on the column 'UTC Date'. (The result is fales) Because at this point Power Query is using local time to make its determination
4.My thinking is to use 'Column1' in Power BI Desktop and 'Column2' in Power BI Service.
You'll get something like the following, with 1 being true and 2 being false
5.Use the USERNAME function to determine if you are in Desktop or Service.
Replace the labeled place with the email address where you log in to Power BI Service
Display column1 in Desktop and column2 in Service.
Desktop:
Service: (Refresh manually first, at this point it will be judged according to UTC time)
Date = 7/1/2024 1:00:00 AM
Column1 = fasle
UTC Date = 6/30/2024 3:00:00 PM
Column2 = true
Hi Anonymous
Thanks for your suggestion - I will try this out and report back
I am in UTC +10 without having to worry about daylight savings. How I get around the problem you describ is to create a query (with enable load as false) that works out what "Today's" date. Here is the code:
First have a simple constant (query with enable load as false) that contains your UTC adjustment which i called "c_LocalTimeAdjust"
let Source = #duration(0,10,0,0) in Source(you could store a parameter that holds the 10/11 or do something smarter with it).
Now for the constant that holds todays date:
Date.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + c_LocalTimeAdjust)This looks more complex than you expect, this is because Power Query tends to hold onto the timezone so its best to scrub it.
Hi Anonymous
Thank you so much for your help and apologies I am really slow with this stuff (still picking up and learning)
Does this solution mean I cannot use the function Date.IsInCurrentWeek?
There are some added benefits for me when using Date.IsInCurrentWeek as it defines the whole week (Monday - Sunday) for me.
I actually posted my whole use case here and the flow:
Your help is really appreciated!
This specific solution gives you a date you can work with to solve many problems. Using the function you mentioned doesn't accept any parameters to help it get around your UTC problem.
In short, if you want to use the function you are locked into UTC. If you take my solution, you need to come up with a solution based on the idea i've given use as its basis.
