Display Reports on Service based on EST rather than UTC no matter what time zone users happen to be

Hi All,

Is it possible to display the reports on Service based on my client's corporate headquarter local time (EST) rather than UTC no matter what time zone the users happen to be in?  I have prepared the workarounds but encountered some problems and would like to have your help. Thank you.

1. Question and Background Informaiton:

I prepared the Quarterly or Yearly Cases reports in Power BI Desktop by connecting to the Dynamics 365 CRM Cases Entity as data source.  The column “createdon” is Date/Time/time zone in type.  

When this column is converted to Date/Time in the Power BI Service, UTC is used.  In other words, after I published the report to Power BI Service the report display on the UTC time. 

Question: It is possible to display reports on Service based on EST time rather than UTC time for all time zones?

Background information:

1. Fiscal Year: Nov 1 to Oct 31
   2018-Quarter 1: Nov 1, 2017 to Jan 31 2018
   2018 Quarter 2: Feb 1, 2018 to Apr 30, 2018.
2. EST time is 5 hours behind UTC in summer and 4 hours behind UTC in winter.

2. An example to explain the issue:

• In this example, there are three cases (in pink) were created on Jan 31 2018 between 20:00 to 20:26 (local EST time) for Bank of Montreal.  So in 2018 Q1 report, Bank of Montreal has 3 cases in total.
• But when converting to Power BI Service using UTC time 4 hours is added (UTC time=“created on” + 4hours)  and it becomes Feb 1 2018 between 0:00am to 2:26 am.  So Power BI Service Display 0 Cases for 2018-Q1 but 3 cases for 2018-Q2.
• This discrepancy of 3 is not correct and they should belong to 2018 Q1 based on EST time. 

3. Will below steps help to offset this small hour difference?

3.1. create a new column called “created on revised”=“created on” minus 5 hours for Summer or 4 hours for winter

3.2. Then, replace “created on” with “created on revised” in my calculation

3.3. When converting to UTC time, 5 hours or 4 hours is added back (based on step 3.1). The report should be the same report based on the EST time.

will these steps work?

4. Below are what I have done

4.1 duplicate column “createdon” -> 1 “createdon - copy”

4.2 created ‘created on revised 1’ use below DAX -> 2

Created On_Revised1 = if(
        month('Cases'[Created On])>=3 && month('Cases'[Created On])<=10, 'Cases'[createdon - Copy]-4/24, 'Cases'[createdon - Copy]-5/24 )
Date Search Inquiry_Rev2019 = 
If(
    Isblank('Cases'[iSight Case Number]), 'Cases'[Created On_Revised], 
            'Cases'[Date Case or Inquiry Received]) 

4.3 change to date/time -> 3

4.4 Replace “created on” with “created on revised” in “Date Search Inquiry_Rev2019” dax

4.5 Updated the relationship replacing “created on” with “created on revised”

4.6 Recalculate the key measure

  Firm (Inquiries) = 
        calculate(
    DISTINCTCOUNT('Cases'[OBSI Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry_Rev2019], Dates[Date]))`Preformatted text`
Sector (Inquiries)_Rev = 
    VAR SECTOR=
      CALCULATETABLE( VALUES('Cases'[Sector Name (merge)]),
       ALL(Dates),
       ALL(Mapping_Sector)
      )
                  
    RETURN
    Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR)

5. Result: this worked and this does not worked

Filter= firm name

Fiscal year 2015: all works

Fiscal year 2016 only display Q1 and Q2

Fiscal year 2017, 2018, 2019 : no data at all

6. Questions: why some works and some not?

7. Outstanding Step:

I still need to publish Power BI report to Power BI Service to validate whether the report is based on EST time now and I have offset the hours difference successfully or not.

Need your help to review and what has been gone wrong. Thank you.