Forum Discussion

Earl40's avatar
Earl40
Icon for Helper I rankHelper I
2 years ago
Solved

Get previous month value from formatted date field

I currently have  date/time column  (short date format 11/27/2023). I then have a column to format it to just show as  "November 2023" (FORMAT(date, "MMMM YYYY") . Is there a way I can add to the formula to change it to automatically show the prior month so it shows October 2023

 

Thank you in advance

Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    Hmm. With the DATEADD function, it will only have dates if there is a previous month in your existing date column - from the docs https://learn.microsoft.com/en-us/dax/dateadd-function-dax. So I think the dateadd function might have been the issue.

     

    To get around this, you can try use FORMAT(EOMONTH('calendar'[Date], -1), "MMMM YYYY")

3 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    You can use the following formula: 

    last month formatted = 
var lastMonthsDate = DATEADD('calendar'[Date], -1, MONTH)
return FORMAT(lastMonthsDate, "MMMM YYYY")

     

     

    • Earl40's avatar
      Earl40
      Icon for Helper I rankHelper I
      2 years ago

      Thanks vicky_ . i tried the formula but the end result just comes up with blank values for the column

      • vicky_'s avatar
        vicky_
        Icon for Super User rankSuper User
        2 years ago

        Hmm. With the DATEADD function, it will only have dates if there is a previous month in your existing date column - from the docs https://learn.microsoft.com/en-us/dax/dateadd-function-dax. So I think the dateadd function might have been the issue.

         

        To get around this, you can try use FORMAT(EOMONTH('calendar'[Date], -1), "MMMM YYYY")