topic FORMAT FUNCTION RETURN WRONG MONTH in DAX Commands and Tips

FORMAT FUNCTION RETURN WRONG MONTH

proavinash — Sat, 23 Nov 2024 15:24:36 GMT

FORMAT FUNCTION RETURN WRONG MONTH

When i am using this format function result showing "January" which is incorrect because today is 23-Nov-2024, please help anyone has solution,

currnetmonth = FORMAT(MONTH(TODAY()),"mmm")

currnetmonth = FORMAT(MONTH(TODAY()),"mmmm")

Re: FORMAT FUNCTION RETURN WRONG MONTH

xifeng_L — Sat, 23 Nov 2024 16:34:47 GMT

Hi proavinash

Because you have an extra MONTH in your formula, this will return the current month 11, and then FORMAT formats the value 11, so it returns January. Because DAX dates from 1900-1-1, so 11 represents 1900-1-11, so return January.

You can try below formula.

currnetmonth = FORMAT(TODAY(),"mmmm")

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: FORMAT FUNCTION RETURN WRONG MONTH

KaeRHa — Sun, 24 Nov 2024 13:33:50 GMT

Hi proavinash ,

The reply you got from xifeng_L is very precise, I will just perhaps offer some more illustration and a general recommendation. If you have issues with outputs of nested functions, it's worth seeing what the nested part actually returns.

So all the 'date part' functions like month actually return an integer, which you can see here:

However, the format function expects a date so when you pass 11 to format function, it is treated exactly as a date so 11th day since the beginning of DAX internal calendar. You can see a similar behaviour e.g. in Excel: if you format a date as a whole number, you will see the nuber of days which passed since thebeginning of Excel's internal calendar.

To avoid this, always pass raw date into format functions like that and result should be as expected:

Hope this can help too 🙂