Forum Discussion

threw001's avatar
threw001
Helper III
2 years ago

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 fi...
Need Help
threw001's avatar
threw001
Helper III
2 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    threw001
    Helper III
    2 years ago

    Thanks so much for your help Anonymous 

    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