In some reports, I need to display the name of the month and sometimes, I need to include the year, for instance:

I will start to explain the simple way which is without a filter and all my examples below, I will use as the current month/year “December 2024” except saying otherwise. To get the result above, I use this formula for the month:

FORMAT(DATE(1,CONVERT(12-1,DATETIME),1),"MMMM")

NOTE:

• To show another month, for instance “September”, change “12-1” by “12-3”
• To show the next month, change “-1” by “+1”

If I also need to show the year, there are 2 ways to do it by using the same formula:

• To put a permanent year:
  FORMAT(DATE(2024,CONVERT(12-3,DATETIME),1),"MMMM YYYY")
• To put an updated year:
  FORMAT(DATE(YEAR(NOW()),CONVERT(12-3,DATETIME),1),"MMMM YYYY")

NOTE: to show another year, for instance “2022”, just change “YEAR(NOW())” by “YEAR(NOW())-2”

Now, I have a calendar table:

To use the formula above, it will be like that:

FORMAT(DATE(1,CONVERT(MAX('Table'[monthID])-1,DATETIME),1),"MMMM")

NOTE: alternatively, I can use VALUES instead of MAX

And to show the year, I will use the updated one:

FORMAT(DATE(YEAR(NOW()),CONVERT(MAX('Table'[monthID])-1,DATETIME),1),"MMMM YYYY")

NOTE: the previous year will display automatically if I change “-1” by “-15” to get “September 2023”

What happens if I will use a filter, for instance, I select “May 2023” in the slicer and I want the measure to show “April 2023”:

As you can see, it is showing correctly the month but not the year so to remediate it, I will use the “monthNB” column of my calendar:

LOOKUPVALUE('Table'[monthnameyear],'Table'[monthNB],VALUES('Table'[monthNB])-1)

NOTE:

• For month, to know how to get the number, read Power BI: calculation based on month and year
• For quarter, I will use the same method by converting the quarter into number, read Power BI: calculate based on automatic dates (quarters, months)
• For year, using this simple formula “YEAR(NOW())-1” is enough