Forum Discussion

IanCockcroft's avatar
IanCockcroft
Post Patron
7 years ago
Solved

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?
 
thanks
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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:

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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's avatar
      IanCockcroft
      Post Patron
      7 years ago

      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
    • kymramosrpo's avatar
      kymramosrpo
      Advocate V
      1 year ago

      💯

      LASTDATE did the trick - thank you so much!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      parry2k
      Super User
      7 years ago

      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's avatar
        IanCockcroft
        Post Patron
        7 years ago

        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