March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
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 @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:
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.
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.
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
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...
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
132 | |
90 | |
88 | |
64 | |
58 |
User | Count |
---|---|
203 | |
141 | |
107 | |
73 | |
70 |