Date settings in Power BI

Question

I have a problem with the date settings in Power BI. Currently I have a dataset which have two colums with the data type "Date", namely LastLogonDate and CreationDate.

In my report I have filtered on the LastlogonDate, where the data looks something like this MM/DD/YYYY. However in some cases it also reads it like DD/MM/YYYY, which result in lastlogdates that are in the future (for example 1st November 2017). I have already changed the Locale to English (United States) as describe in blogpost (without result):

https://community.powerbi.com/t5/Desktop/How-to-change-the-date-format/td-p/40460

So 3/29/2017 is the 29th of March in my report (correct). However this 1/11/2017 is seen as the first of November 2017 (incorrect).

Does somebody else has this problem too or does anyone know the solution to this problem?

marcelbeug · Answer

What data type is your input: text or date alteady?

Did you add "en-US" with every data type change to date?

Can you share some relevant parts of your code?

Are you refering to DAX or to Power Query?

elsie14 · Answer

What data type is your input: text or date alteady?&nbsp;The original input was of type&nbsp;text. I have converted it to type date.&nbsp;&nbsp;Did you add "en-US" with every data type change&nbsp;to date?&nbsp;What do you mean with this? I can't give this parameter when I change the type in the editor.&nbsp;Can you share some relevant parts of your code?&nbsp;This is the code where I changed the type of these columns (from text-&gt;date/time and from date/time to date):&nbsp;&nbsp;&nbsp;&nbsp; #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"LastLogonDate - Copy", type datetime}, {"CreationDate - Copy", type datetime}}),&nbsp;&nbsp;&nbsp; #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"CreationDate - Copy", type date}, {"LastLogonDate - Copy", type date}}),&nbsp;Are you refering to DAX or to Power Query?&nbsp;Power Query

marcelbeug · Answer

Well, you can supply the culture code with every type change, but you must know where to find it.&nbsp;&nbsp;Or you can adjust the generated code by supplying ", &lt;locale&gt;"&nbsp; just before the last ")":&nbsp;    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"LastLogonDate - Copy", type datetime}, {"CreationDate - Copy", type datetime}}, "en-US"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"CreationDate - Copy", type date}, {"LastLogonDate - Copy", type date}}, "en-US"),

elsie14 · Answer

I'm sorry, my last line of the editor was indeed:&nbsp;#"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns2", {{"LastLogonDay", type date}}, "en-US")&nbsp;Unfortunately, also without results. Still reads it as 1st of November 2017:(

marcelbeug · Answer

It's hard to tell, but I found a possible clue:&nbsp;&nbsp;would it be possible that only the DD/YYYY part of the date is converted to date, e.g. after data transformation, especially splitting?&nbsp;I ran the following code, which returns November 1, 2017 for "11/2017" (and&nbsp;errors for "13/1/2017"):&nbsp;let
    Source = {"1/13/2017", "13/1/2017", "11/2017", "1/11/2017"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}, "en-US"),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type with Locale", "Month Name", each Date.MonthName([Column1]), type text)
in
    #"Inserted Month Name"

Forum Discussion

Date settings in Power BI

5 Replies

Related content

Date Setting

Power Query dates

Set default for Date slicer

Find Most Recent Date Relative to another Date Column in Power Query

Power BI setting? Shared folders