What's wrong with excel data model Month function malfunctioning

Valued Contributor
14 Replies


Shouldn't the formula refer to the date column (Week of:) instead of the column with month number?


As a comment:

In DAX day number one is Dec 31, 1899. Thus MONTH(1) returns 12, i.e. December

In Excel day number one is Jan 01, 1900. Thus MONTH(1) returns 1, i.e. January.

that was my first formula =MONTH([COLUMN1])


I only tried to explain why

=FORMAT( 1, "mmmm")

in DAX returns December, not January.


Actually you need to use another field as @Subodh_Tiwari_sktneer suggested.

@Sergei Baklan 


At first it was as what as @Subodh_Tiwari_sktneer suggested  because I'd rather get the translation directly from the original value/date and it was December that's why I tried doing a go between as you can see in the image, just to make sure I wasn't dreaming up things.


Sorry, I missed. Your question is

"What's wrong with excel data model Month function malfunctioning"

The answer is - nothing is wrong with the function, it returns what it shall to return. We may only guess you expect to receive month name of the date, not month name of the month number. If so, instead of

=FORMAT( s1ay[Month], "mmmm" )

it shall be

=FORMAT( s1ay[Week of:], "mmmm" )


@Sergei Baklan 


Like I've said to everyone over and over again. That is where I started:
It started here: =MONTH([Week of:])

was my initial formula
I saw it returned December
so I thought wow 1/7/2021 is returning a December with Month([Week of:]) formula
then I thought let me try doing it a different way:
Which is the one you see in the image.

Is that clear enough for everyone including some new reader to the post?

Here's a file from June 2021:




as you can see the time stamp I created that solution in June of 2021

now I'm showing what I did in the columns except the last one column I put the formula:

FORMAT(MONTH(RMDS[PayDate])),"mmmm") and look what month was returned?

"December" and what is the date in the first column? 1/1/2010


So you can't say I made it up or my application of the formula is incorrect something is up with excel you can tell the world 1/1/2010 returns a December because [what ever the reason here]

so let's scroll down and see if that reason holds...


Oh look all of the dates in January are returning a December and all the dates in February are returning  a January ...



so now tell me what could the reason be?





Your formula is

=FORMAT( MONTH(RMDS[PayDate]), "mmmm" )

if evaluate step by step

=MONTH( RMDS[PayDate] )

returns 1 for the first dates on screenshot


=FORMAT( 1, "mmmm" )

correctly returns December for the first date in DAX calendar.

If change the formula on

=FORMAT( RMDS[PayDate], "mmmm" )

it shall return January for the same date

@Sergei Baklan 

You said:


ok let's give it some benefit of doubt


It gave the correct date which is the same as column 1, but I want the word "January" otherwise what would be the point?  So achieving a word "January" is not achieved by using the FORMAT() function as we've tried it, or something is wrong, but I already did a re-install of Office also using the SetupProd_OffScrub.exe and it didn't fix it otherwise what you're saying is I cannot get a "January" by using the FORMAT([1/1/2010],"mmmm") function?  Is this accurate?

best response confirmed by Yea_So (Valued Contributor)


That could mean you have texts which looks like date, not dates (aka numbers). Please check in Format, is it





@Sergei Baklan 


You are correct the format is text, I need to turn that auto change type so I'll never have to be unsure what the heck happened without my knowledge.  Thank you for your patience sir!!


Uh, great to know the reason was found. I don't know how do you load data to data model, but if at least one record is with text where other are numbers/dates, entire column will be considered as text. It's best practice to define explicitly data types in Power Query on final steps.

@Sergei Baklan 


I turned off the Detect column types and headers for unstructured sources so I won't have to deal with this issue knowing I had to manually set each data type on every PQ I did.



@Subodh_Tiwari_sktneer by the way this formula works:



Thank you for your contribution to solve the problem