Power BI Service: How to create DAX calendar table in Eastern Time Zone

[craigmday]

Hello,

I have created a Calendar table in Power BI Service using DAX for a Power BI report I am creating. The calendar includes columns which check if the row is equal to the current day, current month, current year, etc. These values are used in filtering the Power BI report visuals.

This generally works well except that the calcualtions are based on UTC, so, they are wrong for me when viewing them after 4:00PM Pacific Time (since this is 12:00AM UTC). I have tried to find a solution online, but the syntax does not seem to work for me given how my table is constructed. 

For example, the "Is Current Day" field is calculated by using the Date.IsInCurrentDay function, but this function using UTC:
#"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is Current Day", each Date.IsInCurrentDay([Date])),

Can anyone assist or provide an example of how to create a DAX-based Calendar table with time zone conversion? Alternatively, is there another way to create a dynamic calendar table using Power BI Service (not Desktop)? 

The code for my tables is as follows:

let

StartDate = #date(2024,01,01),

        EndDate =  Date.EndOfYear(Date.AddYears(Date.From(DateTime.FixedLocalNow()),5)),

        Source = List.Dates( StartDate,

                        Duration.Days( EndDate- StartDate) +1,

                        #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"}}),

        #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Start Of Month", each Date.StartOfMonth([Date])),

    #"Added Custom36" = Table.AddColumn(#"Added Custom", "End Of Month", each Date.EndOfMonth([Date])),

    #"Added Custom26" = Table.AddColumn(#"Added Custom36", "Month Year", each Date.ToText([Date],"MMM-yyyy")),

    #"Added Custom27" = Table.AddColumn(#"Added Custom26", "Month Year Sort", each Date.Year([Date])*100 +Date.Month([Date])),

    #"Added Custom1" = Table.AddColumn(#"Added Custom27", "Start Of Week", each Date.StartOfWeek([Date],1)),

    #"Added Custom35" = Table.AddColumn(#"Added Custom1", "End Of Week", each Date.EndOfWeek([Date],1)),

        #"Added Custom2" = Table.AddColumn(#"Added Custom35", "Start Of Year", each Date.StartOfYear([Date])),

        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "End Of Year", each Date.EndOfYear([Date])),

    #"Added Custom31" = Table.AddColumn(#"Added Custom3", "Start Of Qtr", each Date.StartOfQuarter([Date])),

    #"Added Custom32" = Table.AddColumn(#"Added Custom31", "End Of Qtr", each Date.EndOfQuarter([Date])),

    #"Added Custom33" = Table.AddColumn(#"Added Custom32", "Qtr Year", each Text.Combine({"Q", Number.ToText(Date.QuarterOfYear([Date])), "-",Number.ToText(Date.Year([Date]))})),

    #"Added Custom34" = Table.AddColumn(#"Added Custom33", "Year Qtr", each Date.Year([Date])*10 + Date.QuarterOfYear([Date])),

        #"Added Custom4" = Table.AddColumn(#"Added Custom34", "Day Of Week (Tue)", each Date.DayOfWeek([Date],2)+1),

        #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Day Of Week Name", each Date.DayOfWeekName([Date])),

        #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Day Of Year", each Date.DayOfYear([Date])),

        #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Add Days", each Date.AddDays([Date],-3)),

        #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Add Months", each Date.AddMonths([Date],2)),

        #"Added Custom9" = Table.AddColumn(#"Added Custom8", "Current Time", each DateTime.LocalNow()),

        #"Added Custom10" = Table.AddColumn(#"Added Custom9", "Fixed Local Now", each DateTimeZone.FixedLocalNow()),

        #"Added Custom11" = Table.AddColumn(#"Added Custom10", "Current Date", each DateTime.Date(DateTime.FixedLocalNow())),

        #"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is Current Day", each Date.IsInCurrentDay([Date])),

        #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Is Current Month", each Date.IsInCurrentMonth([Date])),

        #"Added Custom14" = Table.AddColumn(#"Added Custom13", "Is Current Year", each Date.IsInCurrentYear([Date])),

        #"Added Custom15" = Table.AddColumn(#"Added Custom14", "Is Previous Day", each Date.IsInPreviousDay([Date])),

        #"Added Custom16" = Table.AddColumn(#"Added Custom15", "Is Previous Month", each Date.IsInPreviousMonth([Date])),

        #"Added Custom17" = Table.AddColumn(#"Added Custom16", "Is Previous Year", each Date.IsInPreviousYear([Date])),

        #"Added Custom18" = Table.AddColumn(#"Added Custom17", "Month Type", each

      if Date.IsInCurrentMonth([Date]) then  "This Month"

      else if Date.IsInNextMonth([Date]) then "Next Month"

        else if Date.IsInPreviousMonth([Date]) then "Last Month"

      else Date.ToText([Date], "MMM-yyyy"))

    in

    #"Added Custom18"

[v-vpabbu]

Hi @craigmday,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[craigmday]

Hi Vinay,

Thank you, I was able to get this to work based on everyone's feedback. This is resolved.

Cheers,

Craig

[v-vpabbu]

Hi @craigmday,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Wikkleyn_81 · Accepted solution]

Hi @craigmday Below is an example on how to add the timezone offset and you can specify what the offset should be.

,
#"Added TimeZoneOffset" = Table.AddColumn(#"Added Custom18", "TimeZoneOffset", each 2), // Adjust the offset as needed
#"Adjusted DateTime" = Table.AddColumn(#"Added TimeZoneOffset", "Adjusted DateTime", each DateTime.AddZone(DateTime.From([Date]), [TimeZoneOffset]))
in
#"Adjusted DateTime"

[v-vpabbu]

Hi @craigmday,

Thank you for reaching out to Microsoft Fabric Community Forum.

Power BI Service operates in UTC, so DAX does not directly support time zone conversions. Because Power BI Service runs on UTC, you need to adjust the date comparison logic by subtracting the time difference between UTC and Eastern Time.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

Vinay Pabbu