cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mvaidya Frequent Visitor
Frequent Visitor

DateTime Issue with M code of Power BI during DayLight Saving Time

Hi Team,

 

I am using below codeto get the latest refresh date of  Power BI report , this  gives proper time in normal days ,but during Daylight saving Time , it didn't change the time.

 

Is there any way where M code can handle it automatically ?

 

let
    #"LAST_REFRESH_DATE" = #table(type table[#"Last refresh date"=text],{{DateTimeZone.ToText(DateTimeZone.ToLocal(DateTime.AddZone(DateTime.LocalNow(),0)),"dd/MM/yyyy hh:mm")}}),
    #"Added Custom" = Table.AddColumn(LAST_REFRESH_DATE, "Custom", each DateTimeZone.SwitchZone(DateTime.LocalNow()))
in
    #"Added Custom"

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DateTime Issue with M code of Power BI during DayLight Saving Time

Hi @mvaidya ,

 

I recommend to create a measure as below to get the latest refresh time instead of M code in power query.

 

Latest time = Now()
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: DateTime Issue with M code of Power BI during DayLight Saving Time

Hi @mvaidya ,

 

I recommend to create a measure as below to get the latest refresh time instead of M code in power query.

 

Latest time = Now()
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mvaidya Frequent Visitor
Frequent Visitor

Re: DateTime Issue with M code of Power BI during DayLight Saving Time

So finally  this problem got resolved after lot many efforts Smiley Happy

Posting  the code , if any one is facing similar issue . This might help.

Below is the code:

What we are doing basically is to find out the last sunday of March and Octor month and if current datetime falls in that add 1 hour , else display as it is .

 

 

let
   UTCOffset = 0,

   Local_Time = DateTimeZone.FixedLocalNow(),

   CurrentYear = Date.Year( DateTimeZone.SwitchZone( Local_Time,UTCOffset)),
   DST_Start = Date.StartOfWeek(#datetimezone( CurrentYear,3,31,0,0,0,UTCOffset,0),Day.Sunday) + #duration(0,2,0,0),
   DST_End = Date.StartOfWeek( #datetimezone( CurrentYear, 10,31,0,0,0, UTCOffset+1,0),Day.Sunday) + #duration(0,2,0,0),

    #"CurrentDate" = Date.From(Local_Time+ #duration(0,UTCOffset,0,0)),
    DST_Start1=Date.From(DST_Start+ #duration(0,UTCOffset,0,0)),
    DST_End1=Date.From(DST_End+ #duration(0,UTCOffset,0,0)),

    #"TimeOffsetinHours"= if #"CurrentDate">= DST_Start1 and #"CurrentDate" <= DST_End1
                            then (UTCOffset) + 1
                          else UTCOffset,

   #"LAST_REFRESH_DATE" = #table(type table[#"Last refresh date"=text],{{DateTimeZone.ToText(DateTimeZone.ToLocal(DateTime.AddZone(DateTime.FixedLocalNow(),-TimeOffsetinHours)),"dd/MM/yyyy HH:mm")}})
in
#"LAST_REFRESH_DATE"

 

Thanks,

Megha Vaidya

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 28 members 898 guests
Please welcome our newest community members: