Sep 30 2021 08:59 PM
Oct 01 2021 12:39 AM
Shouldn't the formula refer to the date column (Week of:) instead of the column with month number?
Oct 01 2021 01:38 AM
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.
Oct 01 2021 07:14 AM
Oct 01 2021 02:11 PM
Oct 01 2021 03:53 PM
Oct 02 2021 04:10 AM
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" )
Oct 02 2021 08:50 AM
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?
Oct 02 2021 09:03 AM
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
Oct 02 2021 09:21 AM
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?
Oct 02 2021 09:31 AMSolution
That could mean you have texts which looks like date, not dates (aka numbers). Please check in Format, is it
Oct 02 2021 09:38 AM
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!!
Oct 02 2021 09:48 AM
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.
Oct 02 2021 09:59 AM
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