cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IanCockcroft
Helper V
Helper V

FORMAT() returns wrong month

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?sc.png
 
thanks
1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

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:

MonthFormat.jpg

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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?

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





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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
Super User II
Super User II

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:

MonthFormat.jpg

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.