Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Rob_Morris
Helper I
Helper I

Measure to show current month or previous month in date table

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?

 

1 ACCEPTED SOLUTION
Lucas_Phil
Helper I
Helper I

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 🙂


View solution in original post

5 REPLIES 5
Lucas_Phil
Helper I
Helper I

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 🙂


@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.

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!

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!

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.