cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clara_magalhaes
New Member

FORMAT a date: error in result

Hello everyone!

 

I have a "calendar table" in my Power BI file, which was built with this command:

 

Calendario = calendar ( date (2015,01,01), today() ).

 

This returns me a table with a list of all dates between 01/01/2015 and today.

 

Later on, I began creating the other columns of the calendar table, getting the year of the date ( YEAR ('Calendario'[Date]) ), the quarter of the date ( QUARTER ('Calendario'[Date]) ), and the month of the date ( MONTH('Calendario'[Date]) ).

 

It worked well, as you can see in the image below (Please be aware that in my example, the columns are using the names in Portuguese for year (Ano), Quarter (Trimestre) and Month (Mes)):

 

ok.jpg

 

BUT, when I try to use the FORMAT command, Power BI is returning the wrong answer.

I am using FORMAT ( 'Calendario'[Mes], "mmm" ), using the columns that I have just created, but I have tried using

FORMAT ( MONTH('Calendario'[Date]), "mmm" ), referencing the date again. They al give me the same answer, as you can see below:

 

nok.jpg

 

Any clues on what is going on? Am I doing something wrong?

1 ACCEPTED SOLUTION
Migasuke
Responsive Resident
Responsive Resident

Hi @clara_magalhaes ,

Could you share your file, at least with the calendar table?

Try to use FORMAT ( MONTH('Calendario'[Date]), "MMM" ) instead and maybe check all Local and Region settings.

View solution in original post

2 REPLIES 2
clara_pr104518
New Member

Hello Migasuke! I was having trouble logging in with my previous account.

This format: FORMAT ( 'Calendario'[Date], "mmm" ) worked well, finally. Apparently I should use a whole date to format, not just the month.

 

Thank you anyway!

Migasuke
Responsive Resident
Responsive Resident

Hi @clara_magalhaes ,

Could you share your file, at least with the calendar table?

Try to use FORMAT ( MONTH('Calendario'[Date]), "MMM" ) instead and maybe check all Local and Region settings.

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!