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
There are a couple of questions and answers regarding the question to apply conditional formatting based on the previous year/month column in a matrix visual.
but i have a different requirement.
My user needs to filter by a slicer for years to view in the matrix columns, for example he selects 2020-2022-2024 in the slicer, so the columns in the matrix have only this 3 years.
and then he wan't to compare the values and apply conditional formatting based if the value of 2022 is going up or down vs 2020 and the same by the other columns.(this is not static, and can even be that there will be a 2 year difference).
Hi @mf5333 ,
Try creating two metrics, one to find the difference between 2020 and 2022 and the other to use as a conditional judgment. Refer to a formula similar to the following:
Difference =
VAR SelectedYears =
VALUES ( 'Sales'[Year] )
VAR Year2020 =
CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Year] = 2020 )
VAR Year2022 =
CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Year] = 2022 )
RETURN
IF (
2022
IN SelectedYears
&& 2020 IN SelectedYears,
Year2022 - Year2020,
BLANK ()
)
Color =
IF([Difference] > 0, "Green", IF([Difference] < 0, "Red", "Yellow"))
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mf5333 This will be tricky = you need to identify exactly what you want the formatting rules to be.
It sounds like you want to compare to the previous year within the filter context? So you'll need to use DAX to do this and need to make sure to KEEPFILTERS on the year, but then clear the visual level filters. Using ALLSELECTED might help with this... it's an interesting question,
Here's a start for a measure that will help you get the previous year, but skip one or two or however many years are skipped in the slicers/filters:
_ fx Format Amount Dynamic Date Compare =
VAR _CurrentYear = SELECTEDVALUE( 'Date'[Year] )
VAR _PreviousYear = MAXX( FILTER( ALLSELECTED( 'Date'[Year] ) , 'Date'[Year] < _CurrentYear ), 'Date'[Year] ) -- Keep slicers and report filters on Year, but get the Year before the one in the visual filter context
RETURN
_PreviousYear
You need to use this in a visual that has Year in rows or columns (based on your description I think it will be in columns).
From here, you can use that _PreviousYear measure to recalculate the amount in the context of the year. @ mention me if you need further help with that part.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
hi thanks for the reply. i try to implement it but can't get it work
Can you help me figure out what my mistake is?
Here is a screenshot of my screen.
Please read about Visual Calculations. They are ideally suited for your scenario.
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 |
---|---|
88 | |
86 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
88 | |
70 | |
66 |