Need help - Is it possible for all users to View Power BI Service Report Based on The Eastern Standard Time no matter what time zone they happen to be in


I have below question and would like to seek help for a workaround.

Steps and Question:

  • I prepared the Quarterly or Yearly cases reports in Power BI Desktop
    I connected Dynamics 365 CRM Cases Entity as data source.
    The column “createdon-copy” is Date/Time/timezone. When one of these column types are converted to Date/Time, the value is automatically localized. When this conversion happens in the Power I Service, UTC is used.

  • Then, I published the report to Power BI Service. By default, Power BI Service users view reports based on the UTC time.

  • Question - is it possible that Power BI Service users view reports based the EST time??

Thank you.

I would like to seek help for a workaround to view the Power BI Service report based on the EST time.

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.

An example to explain the issue:

In this example, there are three cases highlighted in pink.

  • “created on” is Jan 31 EST time and should belong to 2018-Quarter 1

  • But when converting to Power BI Service using UTC time 4 hours is added (UTC time=“created on” + 4hours) Then, based on the converted UTC time, there three cases belong to 2018 Quarter 2.

  • 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’ 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 = 
    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) = 
    DISTINCTCOUNT('Cases'[OBSI Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry_Rev2019], Dates[Date]))`Preformatted text`

Sector (Inquiries)_Rev = 
      CALCULATETABLE( VALUES('Cases'[Sector Name (merge)]),
    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.

Ok number of questions here, quite a bit to review.

By looking at the charts there’s something wrong with your formulas, they are not calculating the correct dates.

It’s difficult for me to say exactly where the problem is though.

Have you look through all your dates and tried to see where they aren’t calibrating correctly? The month and year is not flowing through for each row of dates.

Have you looked at using the DATE function?

This is probably where the issue is if I was to guess

Created On_Revised1 = if( month('Cases'[Created On])>=3 && month('Cases'[Created On])<=10, 'Cases'[createdon - Copy]-4/24, 'Cases'[createdon - Copy]-5/24 )

Yes, the Date function works well. Refer to below screen print Column Number 2 is properly calculated as expected and FY2017 records looks good.

I am not sure why Report display no data for FY 2017 and after FY2017.

I can only recommend trying to reconcile the data more here. It’s something to do with your formula. If you check down the list is every single date coming out correctly?

It might be right in some but not in others.

The other thing to check is your relationship in the model. Is the correct column in the date table lined up with the correct column in your fact table.