I was helping on the Power BI Community and it struck me that there currently was not a way to easily manage Dates and times for Daylight savings. In this blog post below, I will show how I achieved this by using parameters and little bit of M magic (Directly Editing the M Code).

As part of completing this solution I also included the UTC Offset, which I did blog about previously Power BI – Did you know ALL Power BI Services Servers are in UTC? Now how to handle it for Dates & Times which will ensure when the Power BI Desktop file is uploaded and refreshed within the Power BI Service it displays the correct Date and Times.

Setting up the Parameters

The first thing that I did was to set up the parameters as detailed below.

  • Daylight Savings Hour Offset
    • This is for when Daylight savings need to be applied, how many hours must it be offset by.
    • For example, when daylight savings happens and I wind my clock back 1 hour, then the value should be “-1”
    • As you can see above this is set to the Data Type of Decimal Number
  • Daylight Savings Start Date
    • This parameter is for what date does daylight savings start?
    • As you can see above this is set to the Data Type of Date
  • Daylight Savings End Date
    • This parameter is for what date does daylight savings end?
    • As you can see above this is set to the Data Type of Date
  • UTC Offset
    • This is how many hours ahead or behind is your location from UTC.
    • In my Example I am UCT + 10
    • As you can see above this is set to the Data Type of Decimal Number

How I did it in the Query Editor Using M

In the steps below, I will show how I used the Query Editor Language M to get the results I expected.

NOTE: I will be editing the M Directly, so some people might not feel comfortable, so I always suggest making a copy of the M Code and putting I into Notepad, so if something goes wrong, I can copy and paste it back.

  • The first line below is where I am creating storing the output from getting the Date based on the UTC Zone I am in.

    #”Current Date” = Date.From(DateTimeZone.FixedLocalNow()+ #duration(0,#”UTC Offset”,0,0)),

    • The output is going to be stored as #”Current Date” highlighted in GREEN above.
    • Then what I am doing to get the Date based on the UTC time zone is I am adding the duration from the Current Date Time.
      • And here I am using the Parameter I created earlier called #”UTC Offset” highlighted in PURPLE
    • And then on the outside is where I am converting it from Date Time to Date highlighted in ORANGE
    • With the above example if I had to visualize it, the output would be.
  • Now in the next line below is where I used the current date, as well as the parameters for the Daylight Savings Start Date, Daylight Savings End Date and Daylight Savings Hour Offset, which will allow me to determine what the Offset Hours should be.
  • Once again I am storing the output as #”Time Offset in Hours” highlighted in GREEN.

    #”Time Offset in Hours”
    = if #”Current Date”
    >= #”Daylight Savings Start Date” and #”Current Date” <= #”Daylight Savings End Date”
    then (#”UTC Offset”) + (#”Daylight Savings Hour Offset”)
    else #”UTC Offset”,

    • I am using the value #”Current Date” that I populated in the steps above as part of my IF statement highlighted in ORANGE this time
    • For the comparison in the IF statement where the Current Date is greater and equal to the #”Daylight Savings Start Date” and the Current Date is greater and equal to the #”Daylight Savings End Date highlighted in PURPLE
    • If the comparison condition is met or is true (Daylight Savings is in effect) then I am taking the parameter #”UTC Offset” and subtracting this from the Parameter #”Daylight Savings Hour Offset” highlighted in BLUE
      • This is so that it is considering that Daylight savings is in effect so apply the daylight savings offset.
    • Finally, the else part of the IF statement is if the condition is False or not met (It is not daylight savings) highlighted in GREY
      • #”UTC Offset”
  • I then used the #”Time Offset in Hours” within my columns so that it would correctly offset my time when uploading to the Power BI Service, as well as ensuring that it considers Daylight savings.
  • Below is an example of what a step looks like with the #”Time Offset in Hours” being applied

What the Daylight Savings Looks like when being applied

I often find that I can image the output of my data in my head, but this is often a challenge to explain to other people. So below I will demonstrate what the above code will look like when being applied.

  • In this first example I have got the following setup in my Parameters
  • Now if my current date was 10/07/2017 (07Oct 2017) then I would expect the Daylight savings to be applied.
    • Which with the above example being the UTC Offset (10) being added to the Daylight Savings Hours Offset (-1).
    • The outcome should then be 9
  • In this second example I have got the following setup in my Parameters
  • Now if my current date was 10/07/2017 (07 Oct 2017) then I would expect the Daylight savings to NOT be applied.
    • Which with the above example being the UTC Offset (10) being added to the Daylight Savings Hours Offset (-1).
    • The outcome should then be 10
  • In this third example I have got the following setup in my Parameters
  • NOTE: This time I have changed both the Daylight Savings Hour Offset, Daylight Savings Start Date and UTC Offset
  • Now if my current date was 10/07/2017 (07 Oct 2017) then I would expect the Daylight savings to be applied.
    • Which with the above example being the UTC Offset (8) being added to the Daylight Savings Hours Offset (1).
    • The outcome should then be 9

Conclusion

As I have demonstrated how to ensure that your data is always correct and considers Daylight savings timings. This will enable me to ensure that my data is correct and displayed correctly for my end users.

You can find a sample of the file here: Creating Date Table with Fiscal Attributes.pbix