FORMAT() returns wrong month

[IanCockcroft]

Hi guys,

i am trying to return the month name from a date

formula for testing I wrote is

LASTDATE(calender[PK_Date]) & "-"&FORMAT(MONTH(LASTDATE(calender[PK_Date])),"MMM")

LASTDATE(calender[PK_Date])

retuns

2/28/2019 (FEB)

FORMAT(MONTH(LASTDATE(calender[PK_Date])),"MMM")

returns JAN

How is this posible when the Month is clearly FEB?

thanks

[Anonymous]

It seems to be a filter issue. DAX doesnt give wrong answers, it sometimes gives suprising answers, but there is always a reason. Can you upload a sample?

[parry2k]

@IanCockcroft Are you adding this as a column? If yes then you don't need to use LASTDATE function. use following if adding as a column

Month = FORMAT (  calender[PK_Date]  , "m/d/yyyy-mmm" )

[IanCockcroft]

Hi parry2k, its actually for a dynamic label within a card. Once I get this right, i will have 2 labelS, 1 for current name of year and month and one for previous name of year and month. The actual vallues for these 2 are measures in a matrix with the column header removed.

Because it needs to return a single value, I added LASTDATE to illmintae all the other dates.

Thanks for taking the time to have a look.

cheers

Ian

[jdbuchanan71 · Accepted solution]

Hello @IanCockcroft 

You don't actually need to call the month for formatting your date the way want, you can just use

dispPevMnthScenario = FORMAT ( LASTDATE ( calender[PK_Date] ) , "m/d/yyyy-mmm" )

The reason you are seeing Jan in your string is because you used MONTH in your formula.

The MONTH of 2/28/2019 is 2

PowerBI sees numbers as a date so when you format 2 as MMM it converts 2 to a date of 1/1/1900 then gives you the mmm of that date which is Jan.  Here is an image showing both:

[IanCockcroft]

Thanks jdbuchanan71.

The final measure code is

dispPrevMnthScenario =

var scenario1 = SELECTEDVALUE('Scenario type'[Scenario])

var PrevYearMonth = FORMAT ( DATEADD(FIRSTDATE(Calender[PK_Date]), -1, MONTH) , "yyyy") &"-"&FORMAT ( DATEADD(FIRSTDATE(Calender[PK_Date]), -1, MONTH) , "mmm")

return

PrevYearMonth & " "& scenario1

This is used to change column headers depending on selections. budgets, actuals, scenarios

thanks a mil

Ian