Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Christian ArltX]

My Power BI Friends – help! 

I have a table that shows the count sales by product/site the provides a new record every 5 minutes. There are multiple products and site and I’m trying to find the easiest way to complete the below 2 steps. There is another table (site that lists the related time zone)

Example

ID	Created At	Count	Product	Site
1	9/4/2019 1:15:23 am +00:00	5	1	1
2	9/4/2019 1:10:23 am +00:00	10	1	1
3	9/4/2019 1:05:23 am +00:00	17	1	1

Part 1) Convert the time of (Created At) to local

If today’s date is in Daylight Savings then UTC – Daylight Savings Conversion else Normal Conversion

Time Zone		Normal Conversion	Daylight Savings Conversion
America/New_York	Eastern Daylight Time	UTC-5	UTC-4
America/Chicago	Central Daylight Time	UTC-6	UTC-5
America/Los_Angeles	Pacific Daylight Time	UTC-8	UTC-7

Part 2) Grouping Results by:

Show the value for the max time per day by site by product

——————————
Beau Arlt
Business Analyst
Holland MI
989-400-7812
——————————

[Riccardo Perico]

Hi Beau,

You could take a look at this post https://community.powerbi.com/t5/Desktop/How-to-change-time-on-Column-according-to-daylight-savings/td-p/519221 and reusing part of this approach to create a new calculated column using the desidered time zone.

I would create a calculated column on your calendar table specifying if the day is daylight.
Then you can create a measure like this:

Today Is Daylight = CALCULATE(MAX(‘Calendar'[Is Daylight]), ‘Calendar'[Date] = TODAY())

And then you can create a calculated column on your “fact table” that switch timezones according to the value of that measure. I mean
Colum something like this:

Converted Time = if([Today Is Daylight] = 0, ‘Fact’2026 + (4/24), ‘Fact’2026 + (5/24))

Changing the code a bit, I think you can include also the territory management.

hope this helps.

riccardo

——————————
Riccardo Perico
Data Platform & BI Specialist @ SolidQ
——————————
——————————————-

[Ryan Perry]

Hi ,

Timezone conversion is something that took some work in our system. Converting to local from UTC is complicated by DST.  Also, you may note that if you are running on your local machine (IE when developing the report), the results will differ from what is shown on the PBI server once the data refreshes.  This is because PBI gets the time from the machine upon which it is running, and does not allow you to specify what TZ to use. (Apparently this is due to the Power Query not supporting this feature. It is actually PQ that is limited). The result is that users in different time zones will see slightly different results (IE U.S. sales reps turn in deals during the last few hours of the month, which show up as being in the following month on PBI server due to it running in UTC).  If you search the threads, you’ll probably find one with more info from when I worked through this.

My solution, which can adjust to a specified TZ, and will account for DST, can be found at: https://github.com/ryanperrymba/PowerBIConvertUTCtoLocalTime
Take it as is. As far as I can tell, it works correctly, but I cannot guarantee results.  Happy to chat if you have questions, and of course if you can improve on it in any way, that’d be aweseome too.

——————————
Ryan Perry
Business Systems Analyst
Auric Solar
West Valley City UT
(801) 878-3363
——————————
——————————————-

[Ryan Perry]

Clarification: Users on the PBI server will see the same thing on the service. But it will not match what they expect to see in their local application. IE Dynamics adjusts timezones automatically. PBI just runs as UTC.  While you can convert the PBI report to a single time zone, each user in a different Timezone will still have to deal with the descrepancy between the reporting timezone of PBI service and what they see in their application, which is likely adjusted to their timezone.

——————————
Ryan Perry
Business Systems Analyst
Auric Solar
West Valley City UT
(801) 878-3363
——————————
——————————————-

[Riccardo Perico]

 you’re right.
Also Reza Rad talked about this some time ago:
https://radacad.com/solving-dax-time-zone-issue-in-power-bi#targetText=Power%20BI%20is%20a%20cloud,will%20fetch%20server's%20date%2Ftime. ?

——————————
Riccardo Perico
Data Platform & BI Specialist @ SolidQ
——————————
——————————————-

[Ryan Perry]

Thanks , Reza Rad has awesome content.  The PBI team is aware of this limitation as well. When we last chatted, they were pushing the idea to the PowerQuery team to see if support couldn’t be added to the M language library to handle DST TZ conversion. At least that was my understanding of the internal discussion at MSFT.???? Given his prominence, maybe Reza knows of any updates from MSFT? ??

——————————
Ryan Perry
Business Systems Analyst
Auric Solar
West Valley City UT
(801) 878-3363
——————————
——————————————-