FORMAT FUNCTION RETURN WRONG MONTH

[proavinash]

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

[xifeng_L · Accepted solution]

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~

[KaeRHa · Accepted solution]

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 🙂

[xifeng_L · Accepted solution]

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~