Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
I'm trying to show a previous month value in a card but it shows the latest month
The measure i'm using works fine in a table so i'm a little bit stumped??
Solved! Go to Solution.
Hi @Anonymous ,
Sorry, I didn't think carefully enough. I have modified it:
Measure =
var _a = MAX('Date_table'[Date])
var _b = EDATE(_a,-1)
return CALCULATE (
SUM ( Nominal_Roll[FTE] ),
FILTER (
ALL('Nominal_Roll'),
YEAR('Nominal_Roll'[Extract_date]) = YEAR(_b)&&
MONTH('Nominal_Roll'[Extract_date])= MONTH ( _b )
)
)
Ouptup:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Sorry, I didn't think carefully enough. I have modified it:
Measure =
var _a = MAX('Date_table'[Date])
var _b = EDATE(_a,-1)
return CALCULATE (
SUM ( Nominal_Roll[FTE] ),
FILTER (
ALL('Nominal_Roll'),
YEAR('Nominal_Roll'[Extract_date]) = YEAR(_b)&&
MONTH('Nominal_Roll'[Extract_date])= MONTH ( _b )
)
)
Ouptup:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please try:
Measure =
CALCULATE (
SUM ( Nominal_Roll[FTE] ),
FILTER (
ALL('Nominal_Roll'),
YEAR('Nominal_Roll'[Extract_date]) = YEAR(MAX('Date_table'[Date]))&&
MONTH('Nominal_Roll'[Extract_date])= (MONTH ( MAX ( Date_table[Date] ) ) - 1)
)
)
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jianbo,
Your solution works for me in a card, but when I add one more slicer which is Hierarchy 1, the previuos month doesn’t change at all. For example, if I select Hierarchy 1 “B”, the previous month should be 13, but it still shows the whole month.. Can you help on this?
Please see the attched samples:
Sample Raw Data - Previous Month.xlsx
Thanks,
Mei
Many thanks for this, it still didn't work though unfortunately.
However if i change the "-1" to "-2" it gives me the correct answer which i don't quite understand.
One problem i can foresee with this formula though. I'm not sure it will work when we get to January?? I don't think the "-1" would give me Decembers figure?
Hi @Anonymous ,
I can't reproduce your issue in my side. Could you please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here's a snapshot of my table below where the Extract_date is related to my date table
Hi @Anonymous ,
PREVIOUSMONTH function returns a table that contains a column of all dates from the previous month, based on the first date in the Dates column, in the current context.
The context of the card and the table is different. In this condition, the result is showing blank.
Please try something like this:
CALCULATE (
SUM ( Nominal_Roll[FTE] ),
FILTER (
Date_table,
MONTH ( Date_table[Date] )
= MONTH ( MAX ( Date_table[Date] ) ) - 1
)
)
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply but unfortunately it's still returning blank
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
91 | |
89 | |
77 | |
69 | |
69 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |