Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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 🙂
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 😅
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
64 | |
55 |