SOLVED

What's wrong with excel data model Month function malfunctioning

%3CLINGO-SUB%20id%3D%22lingo-sub-2801510%22%20slang%3D%22en-US%22%3EWhat's%20wrong%20with%20excel%20data%20model%20Month%20function%20malfunctioning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2801510%22%20slang%3D%22en-US%22%3E%3CP%3Eis%20this%20a%20known%20issue%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1633060662849.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F314106i66F219E7AF5930E8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1633060662849.png%22%20alt%3D%22Yea_So_0-1633060662849.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2801510%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2801856%22%20slang%3D%22en-US%22%3ERe%3A%20What's%20wrong%20with%20excel%20data%20model%20Month%20function%20malfunctioning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2801856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShouldn't%20the%20formula%20refer%20to%20the%20date%20column%20(%3CSTRONG%3EWeek%20of%3A%3C%2FSTRONG%3E)%20instead%20of%20the%20column%20with%20month%20number%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2802111%22%20slang%3D%22en-US%22%3ERe%3A%20What's%20wrong%20with%20excel%20data%20model%20Month%20function%20malfunctioning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2802111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%3A%3C%2FP%3E%0A%3CP%3EIn%20DAX%20day%20number%20one%20is%20Dec%2031%2C%201899.%20Thus%20MONTH(1)%20returns%2012%2C%20i.e.%20December%3C%2FP%3E%0A%3CP%3EIn%20Excel%20day%20number%20one%20is%20Jan%2001%2C%201900.%20Thus%20MONTH(1)%20returns%201%2C%20i.e.%20January.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2802835%22%20slang%3D%22en-US%22%3ERe%3A%20What's%20wrong%20with%20excel%20data%20model%20Month%20function%20malfunctioning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2802835%22%20slang%3D%22en-US%22%3Ethat%20was%20my%20first%20formula%20%3DMONTH(%5BCOLUMN1%5D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2802843%22%20slang%3D%22en-US%22%3ERe%3A%20What's%20wrong%20with%20excel%20data%20model%20Month%20function%20malfunctioning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2802843%22%20slang%3D%22en-US%22%3Eit%20seemed%20to%20work%20on%20this%20time%20period%20thing.%20I'll%20have%20to%20see%20if%20it%20will%20work%20still%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsocial.technet.microsoft.com%2Fwiki%2Fcontents%2Farticles%2F1018.powerpivot-grouping-by-dates-months-quarters-years-etc-in-pivottables.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsocial.technet.microsoft.com%2Fwiki%2Fcontents%2Farticles%2F1018.powerpivot-grouping-by-dates-months-quarters-years-etc-in-pivottables.aspx%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2804253%22%20slang%3D%22en-US%22%3ERe%3A%20What's%20wrong%20with%20excel%20data%20model%20Month%20function%20malfunctioning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20only%20tried%20to%20explain%20why%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFORMAT(%201%2C%20%22mmmm%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20DAX%20returns%20December%2C%20not%20January.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EActually%20you%20need%20to%20use%20another%20field%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bsuggested.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Valued Contributor
14 Replies

@Yea_So 

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

@Yea_So 

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])

@Yea_So 

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.

@Yea_So 

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:

Yea_So_0-1633189319687.png

 

Yea_So_1-1633189364723.png

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 ...

Yea_So_2-1633189812072.png

 

so now tell me what could the reason be?

 

 

 

@Yea_So 

Your formula is

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

if evaluate step by step

=MONTH( RMDS[PayDate] )

returns 1 for the first dates on screenshot

Next

=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:

Yea_So_0-1633191304255.png

ok let's give it some benefit of doubt

Yea_So_1-1633191367218.png

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)
Solution

@Yea_So 

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

image.png

or

image.png

 

@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!!

@Yea_So 

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.

Yea_So_1-1633193612483.png

 

@Subodh_Tiwari_sktneer by the way this formula works:

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

Yea_So_0-1633193550092.png

Thank you for your contribution to solve the problem