Measure to show current month or previous month in date table

[Rob_Morris]

I'm looking to see if it is possible to have a calculated field / measure in a date table that shows if the date in question is either in the current month, or in the previous month based on the current date - so that it can be used in a slicer.

I have a list of dates but would like it to show as below

Date                               MonthCheck

11/15/2022                   Current Month

10/30/2022                   Previous Month

09/03/2022                   

Is this possible with DAX, and if so, how?

[Lucas_Phil · Accepted solution]

Hi @Rob_Morris !

I created a calculated columnd for this and it worked as expected, please try on yours:

MonthCheck = IF ( MONTH([Date]) = MONTH (TODAY()) , "Current Month", "Previous Month")

Don't know if you are going to work just with 2022 or with past years as well.
If so, just need to add another IF, something like that should work just fine: 

MonthCheck = 
IF ( YEAR(CPM_Data[Date])=YEAR(TODAY()),
    IF ( MONTH(CPM_Data[Date])=MONTH(TODAY()), "Current Month", "Previous Month"
       ), 
    "Previous Year"
    )

Tell me later if it worked 🙂

[Rob_Morris]

@Lucas_Phil Just one last question - if I wanted to go -2 months and have this highlighted as "-2 months" how would I amend the coding to show this?

Sorry I'm a complete newbie when it comes to DAX.

[Lucas_Phil]

Don't worry!

You could add another "IF" inside, with like something for '-2 months'.
When we use de MONTH function, it returns de number of the month, so we can use it to sub or add with other value.
Something like:

 IF ( MONTH(CPM_Data[Date])-MONTH(TODAY()) = 0, "Current Month",
    IF ( MONTH(CPM_Data[Date])-MONTH(TODAY()) = -1, "-1 Month",
           IF ( MONTH(CPM_Data[Date])-MONTH(TODAY()) = -2, "-2 Month",
 "Previous Month"
       )))

If you started to have a lot of conditions, maybe it will be better combine VAR with a SWITCH function, like this:

MonthCheck = 

VAR month_difference = MONTH(CPM_Data[Date]) - MONTH(TODAY())

VAR month_check = SWITCH(TRUE(),
    month_difference = 0, "Current month",
    month_difference = -1, "-1 month",
    month_difference = -2, "-2 month",
    month_difference = -3, "-3 month", "A lot of months ago"
    )

Return month_check

You could use VAR in the first example as well, it would be IF ( month_difference = 0, ... , IF month_difference = -1, .... , ....)
Something like that!

[Rob_Morris]

Brilliant - this has worked perfectly!  Thank you.

Also noticed in the past I was trying to create measures instead of calculated columns - which didn't help matters!

[Lucas_Phil]

When to use measures or calculated columns still get me sometimes as well 😅