cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wang123 Regular Visitor
Regular Visitor

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:2019-01-11_0-57-51 - diff.png

 

 

 

 

 

 

  • 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

2019-01-11_1-43-45 step 1.png

 

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 revised2019-01-11_1-55-08 relaship.png

 

 

 

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 all2019-01-11_2-05-50 last.png

 

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
wang123 Regular Visitor
Regular Visitor

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

I tried to use "Created On_revised" to replace "Created on_Revised1" and the report are now all display well.

 

Created On_Revised = Date(year('Cases'[Created On_Revised1]),month('Cases'[Created On_Revised1]),day('Cases'[Created On_Revised1]))

After I published the report to Service the Report display based on the EST time.  Thank you.

4 REPLIES 4
Community Support Team
Community Support Team

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

Hi @wang123

After testing with this tool which converts EST time to UTC time, in 4~10, EST time=UTC time-4, in 11~3, EST time=UTC time-5.

 

in your example, if "createdon - copy" is EST time, ‘created on revised 1’ is UTC time, then for example:

"createdon - copy"                           ‘created on revised 1’              

11/5/2015  4:35:36 PM                      11/5/2015  9:35:36 PM

however, in your test, it doesn't look like this.

 

Your formula:

Created On_Revised1 seems to be not correct, either i don't understand its logic, or it can't be format correctly in the offical DAX Formatter tool.

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])

Best Regards

Maggie

wang123 Regular Visitor
Regular Visitor

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

Thank you Maggie, @v-juanli-msft

 

The purpose of createdon_revised is to offset the difference when createdon converting to UTC.  Does this make sense?  

 

"1. createdon - copy"                   3. UTC ="createdon_revised"+5 hrs                 4. difference between UTC and EST

11/5/2015  4:35:36 PM                   11/5/2015  9:35:36 PM                                             +5 hours

 

 

"1. createdon - copy"         ‘2. createdon revised’ ("createdon-copy" - 5hrs)   3. UTC ="createdon_revised"+5 hrs      4. Difference

11/5/2015  4:35:36 PM      11/5/2015  11:35:36 AM                                            11/5/2015  4:35:36                                -5+5=0 hours

 

wang123 Regular Visitor
Regular Visitor

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

I agree with you and I am not sure about the the formula of "Created On_Revised1" although the logic is clear to me:

if 10<=Month of "createdon">=3

       then = "createdon" minus 5 hours,

       otherwise = "createdon" minus 4 hours

 

The fomular resulte in below screen print "EST Winter(UTC-5) " has no syntac error.  The result seems correct.  And the "UTC(new)" stimulate the UTC time on Service after converting - also looks good.   

 

2019-01-09_9-04-45---good.png

 

 Below Visulaiton (Bar Chart on the left) is created based on the above formula.   Since we have 2015 and partial of 2016 data are populated correctly I will assum that the Formular works.  But I am wondering why Fiscal Year 2017 and after are not populating properly on Visualiztaion while the createdon-revised fields are all calculated. 

2019-01-11_2-05-50 last.png

 

 

 

 

wang123 Regular Visitor
Regular Visitor

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

I tried to use "Created On_revised" to replace "Created on_Revised1" and the report are now all display well.

 

Created On_Revised = Date(year('Cases'[Created On_Revised1]),month('Cases'[Created On_Revised1]),day('Cases'[Created On_Revised1]))

After I published the report to Service the Report display based on the EST time.  Thank you.