Date extraction and conversion

[vitor_fontana]

Hello everyone!

I was facing a lot of issue regarding DATE type on my Dashaboard and wasn't understanding why PowerBi was not returning to me the correct Date value, so I've made a test table to check how it was working with Date values and the same strange unkown issue happened. Follow below what I did (and really expect that some of you could explain to me why it happens):

I've just used TODAY(), MONTH(), FORMAT() expressions and some simple extractions from a Date column. My date settings are Brazilian Portuguese, so our Date pattern here is "dd-mm-yyyy".

I've created one table called TestDate;
First column: Today = TODAY()
Result: 01/06/2021 00:00:00

Second column: Month_Today = MONTH(TODAY())

Result: 6

Third column: Format_Month_Today = FORMAT(MONTH(TODAY()),"dd-mmmm-yyyy")

Result: 05-janeiro-1900

Fourth column: ExtractFromTable = 'TestDate'[Today].[Month]

Result: June

Fifth column: Format_From_extracted = FORMAT('TestDate'[Today].[Month], "mmm")

Result: June

My doubts regarding this are:
1. Why the third column show this date?
2. Why the fifth column is showing "June" instead of "Jun" as requested on FORMAT?

Thanks!

[vitor_fontana]

Thanks for answering HotChilli, it explains why it's happening! Thanks a lot!
I'll look for a way to correct convert numbers to apply it correctly to my formulas.  😉

[HotChilli · Accepted solution]

1. The code is sending the number 6 to be formatted as a date so that's 6 days after the last day of 1899 in numeric powerbi dates, which equals 5th January 1900

2.  "mmm" formats a date but it's not getting a date, it's getting either a month name or a number (not sure) from the inbuilt powerbi date table with [.Month] so it's probably just returning the month name