Date Transformation Issue in Power BI Service

[Anonymous]

While generally  Developer will be fine selecting the Level 01 value for a given date characteristic, if you are planning to share a report/dashboard published in the power bi service that will be viewed across time zones with users expecting to automatically have different date formats (Ex: DD/MM/YYYY vs MM/DD/YYYY) populated, it would makes sense to select the “Key” property in addition to the “Level 01” value and use the “Key” value in any reports/calculations.

While this might seem like having duplicate information when working with data in the Power BI Desktop, in some cases this can affect published reports with scheduled refreshes in the Power BI Service. Below is an example where both the Level 01 and Key fields are imported into the Power BI service with a date format.

PBI Desktop – When displayed in the Power BI Desktop everything appears fine.

PBI Service (After A Scheduled Refresh) – When end-users natural settings are for European dates August 9th, 2019 (08/09/2019) could be converted to September 8th, 2019 (09/08/2019) with the values that could not be readily converted (days > 12) are left blank if using the Level 01 field. This can cause significant errors in formulas if you are creating your own calculated fields or measures. This can be corrected by using the Key field as the date in all reporting/calculations.

If the issue is not resolved , Create a custom column by using the formula 

CreatedOn Key=Date.FromText(Text.End([CreatedOn],4)&","&Text.End(Text.Start([CreatedOn],5),2)&","&Text.Start([CreatedOn],2)  ) 

Use this Custom Column in the report.   

Thanks,

Shanthakumar.