Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
2. An example to explain the issue:
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.
Solved! Go to Solution.
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.
Hi @Anonymous
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
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.
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.
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.
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