PowerBI Service Timezone incorrect when checking if date is in current week

[threw001]

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

[RossEdwards]

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.

[threw001]

Hi @RossEdwards 

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:

https://community.fabric.microsoft.com/t5/Desktop/PowerBI-Service-Timezone-incorrect-when-checking-i...

Your help is really appreciated!

[RossEdwards]

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.

[threw001]

Hi @v-zhouwen-msft 

Thanks for your suggestion - I will try this out and report back

[v-zhouwen-msft]

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.

www.fourmoo.com

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.

[threw001]

Hi @v-zhouwen-msft 

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

[v-zhouwen-msft]

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

[threw001]

Thanks so much for your help @v-zhouwen-msft 

Im so sorry I dont quite follow, I am doing all of my functions and checks within Power Query and not the front end PowerBI

If it helps, I can provide more info on the whole process and my desired end results...

• My week starts on Monday and ends on Sunday
• I have a field called "RelativeWeek" that I use in order to filter on which week to display, the nice thing about this "RelativeWeek" field is that is adjusts based on current date/time.
• I show my data in complete weeks rather than rolling week, so for example, if my "RelativeWeek" filter is set to -1, it will show data for all of last week Monday - Sunday (17/06/2024 - 23/06/2024) no matter if it is 24/06/2024 or 30/06/2024.

To achieve this I have the following set up (All within Power Query):

Query 1 (Input Query)

The query below that lists every single Monday starting from 01/01/2016. This is where I use the function Date.IsInCurrentWeek that checks whether or not the date (Monday) is in the current week.

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"

Dates Query

I then have my Dates Query - this lists all dates going forward from 01/01/2016. It uses Query 1 to test whether or not the date is in the current week and has my "RelativeWeek" field that I use.

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])),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"yearWeek"}, WeekCount, {"yearWeek"}, "WeekCount", JoinKind.LeftOuter),
    #"Expanded WeekCount" = Table.ExpandTableColumn(#"Merged Queries", "WeekCount", {"WeekCounter", "IsCurrentWeek"}, {"WeekCount.WeekCounter", "WeekCount.IsCurrentWeek"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded WeekCount", "CurrentWeek", each Table.SelectRows(WeekCount, each ([IsCurrentWeek] = true)){0}),
    #"Expanded CurrentWeek" = Table.ExpandRecordColumn(#"Added Custom1", "CurrentWeek", {"WeekCounter"}, {"CurrentWeek.WeekCounter"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded CurrentWeek", "RelativeWeek", each [WeekCount.WeekCounter]-[CurrentWeek.WeekCounter]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"RelativeWeek", Int64.Type}})
in
    #"Changed Type"

 I use my Dates Query and merge this with other data sets that have dates. This means that I can pull in the "RelativeWeek" into these queries and quickly filter to things that happened -1 Week, -2 Weeks, etc...

This works great, the only issue I have is that the weeks do not tick over to the next week until around 10/11am AET time as per listed in my first post. After 10/11am it works fine..

Thank you