Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I need to get the previous month from a slicer. I'm currently using this measure. Its gets the current month but I really need the previous month. Can someone show me how to change this to always retrieve the previous month?
Solved! Go to Solution.
Thank you parry2k
Hi, @U156531
According to your description, I created three months of sample data:
First, I created a date table using the following DAX expression:
DateTable = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),TODAY()),
"Year",YEAR([Date]),
"Month",MONTH([Date]),
"YearMonth",FORMAT([Date],"YYYY-MM")
)
I created a new calculated column in the Date table using the following DAX expression:
MTDwithcur = IF(
YEAR([Date])=YEAR(TODAY())&&MONTH([Date])=MONTH(TODAY()),
"CurrentMonth",
IF(
YEAR([Date])=YEAR(TODAY())&&MONTH([Date])+1=MONTH(TODAY()),
"Previous Month",
FORMAT([Date],"YYYY-MMMMM")
)
)
I created the following relationship between the date table and the fact table:
The results are as follows:
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you parry2k
Hi, @U156531
According to your description, I created three months of sample data:
First, I created a date table using the following DAX expression:
DateTable = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),TODAY()),
"Year",YEAR([Date]),
"Month",MONTH([Date]),
"YearMonth",FORMAT([Date],"YYYY-MM")
)
I created a new calculated column in the Date table using the following DAX expression:
MTDwithcur = IF(
YEAR([Date])=YEAR(TODAY())&&MONTH([Date])=MONTH(TODAY()),
"CurrentMonth",
IF(
YEAR([Date])=YEAR(TODAY())&&MONTH([Date])+1=MONTH(TODAY()),
"Previous Month",
FORMAT([Date],"YYYY-MMMMM")
)
)
I created the following relationship between the date table and the fact table:
The results are as follows:
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@U156531 not sure if I understood but try this:
MTDwithCur =
VAR __EOMThisMonth = EOMONTH( TODAY (), 0 )
VAR __EOMLastMonth = EOMONTH ( TODAY(), -1 )
VAR __EOMDate = EOMONTH ( Table[Date], 0 )
RETURN
SWITCH (
TRUE (),
__EOMDate = __EOMTHisMonth = "Current Month",
__EOMDate = __EOMTPrevMonth = "Previous Month",
FORMAT ( __EOMDate, "yyyy mmmm" )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I tried using data type Text, Date and whole number but I keep getting this error.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |