Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date data/datetype changes on refresh in PowerBI service

Hi all,

 

I have a situation where I need some help with, I hope someone has a solution for it.

 

I have created a report based on a date type DD-MM-YYYY.


Sidenote:
and change this to a different format "WK"&

WEEKNUM('dDate Report'[Date],21)&
FORMAT('dDate Report'[Date],"YY") which gives me for example WK 01 2021.
 
When I create my column chart on the PowerBI desktop I get the result below, which is correct.
 
When I publish my report on PowerBI service then it still shows the same/ the correct way.
But when I refresh my report in PowerBI service it somehow changes date types and some dates get "shuffled" as bellow:
Here you can see that week 5,6&9 are gone and week 40 and 41 have much greater value, so it looks like week 5 & 6 or 9 are added to week 40 and 41..

To investigate my issue I have added a date filter to my report to check how the dates are calculated:
 

What I can see is that dates like 19-2, 26-2,19-3 and 26-3 are correct, and 5-2,5-3,12-2 and 12-3 are "shuffled" during the refresh in the PowerBI service.

Anyone have a clue how this is possible/ how I can solve this?
 
Thank you in advance!
 
Kind Regards,
Ramon
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I solved my issue....

    From PowerQuery I had transported the date as a text value and transformed the text value to a date in PowerBi desktop. which caused that in some cases the day and month where swapped.

    Right now I have set the field type to date in PowerQuery and my issue is solved.

     

    Kind Regards,

    Ramon

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak ,

    Thank you for your fast response, I have checked the link and tried to setup de right settings. But it didn't solve my issue:
    I have checked the PowerBi service setting its on Default (browser language)
    The Chrome settings are on Englisch (UK) which should have DD/MM/YYYY
    And I have cleared cache and published/ tried again.

    Any other ideas?
    See images bellow:

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I solved my issue....

    From PowerQuery I had transported the date as a text value and transformed the text value to a date in PowerBi desktop. which caused that in some cases the day and month where swapped.

    Right now I have set the field type to date in PowerQuery and my issue is solved.

     

    Kind Regards,

    Ramon

  • MyriamW's avatar
    MyriamW
    Frequent Visitor
    2 years ago

    I have a similar problem. I have a date value coming from an Azure SQL DB. I want to force a specific date format (dd.mm.yyyy) in PBI Service, so I load the dates into Query Editor and afterwards convert them into Text in Power BI Desktop. Publishing to the service gives me the right result, but when the dataset is refreshed, it changes to english format ("mm/dd/yyyy).

    Why does this happen and what can I do? I really don't want to write a DAX FORMAT Measure for all my date columns! But at the moment I do not see some other solution...