DAX measure to get month number for 2 months previous

[PhoenixBird66]

Hi everyone,

I need a DAX measure that will return the month number of 2 months prior to the current month.

In order to do this previously I was using this measure:

2MonthsAgo = FORMAT([Today],"mm")-2

However, now that we're in January and the month number is 1, it's giving me the incorrect value of -1 instead of 11. When we're in February, I need the measure to return 12 (the Month number for December).

Welcoming any suggestions for how I should get around this issue?

[parry2k · Accepted solution]

@PhoenixBird66 I assume TODAY Is of type date, try this

Last 2 month = 
FORMAT ( EORMONTH ( [TODAY], -2 ), "MM" )

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak · Accepted solution]

@PhoenixBird66 , Try like

2MonthsAgo = FORMAT(eomonth(today(),-2),"mm")

or

2MonthsAgo = month(eomonth(today(),-2))

[parry2k · Accepted solution]

@PhoenixBird66 I assume TODAY Is of type date, try this

Last 2 month = 
FORMAT ( EORMONTH ( [TODAY], -2 ), "MM" )

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡