Conditional formatting for Period over Period change in a Matrix

[ashishd]

Hello friends,

Sorry, I am unable to share the PBIX file for this request but I think that this is a regular requirement and experts in this forum would be able to provide their kind guidance for the same.

The period in the column can be changed to Month/Quarter/Year and even the values displayed can be dynamically changed with the parameters.

The task is to color code the values in the next period based on their increment/decrement. So, it is basically color code for MoM/QoQ/YoY change (or %change) for a given row.
The Quarter is a text field here because the display format required is "Q1-2024".

Please suggest how to go ahead here.

Thank you!

Ashish

[amitchandak]

@ashishd , you can use measure based conditional formatting

if you are using field parameters refer

Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf

You can measure like

selectedAxis = maxx(FILTER('Axis Slicer', 'Axis Slicer'[Axis Slicer Order] = SELECTEDVALUE('Axis Slicer'[Axis Slicer Order])), 'Axis Slicer'[Axis Slicer])

Color = Switch( True() ,
selectedAxis = "Month", Switch(True() , [MOM %] >0 , "Green", "Red") ,
selectedAxis = "Quarter Year", Switch(True() , [QOQ %] >0 , "Green", "Red") ,
Switch(True() , [YOY %] >0 , "Green", "Red")
)

Use the color measure in conditional formatting using field value option

[ashishd]

Hi @amitchandak  - Thank you for the reply.

Sorry, I seem to have missed asking one part of it. The first question should have been how to get the PeriodoverPeriod change with the above matrix layout as the period is dynamic and not all the period are of date type (quarter in my case is text, month is date and year is numeric).
So, I don't want the %changes to be displayed but I want the %changes values for color coding the raw values in a row.
Your above solution will come after I achieve this first step. Any guidance on this?

Regards,

Ashish

[Anonymous]

Hi, @ashishd 

You can try the following methods. Add a column to the calendar table about quarterly sorting.

Quarter Number = YEAR([Date])*100+QUARTER([Date])

Measure = 
Var _Sum1=CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Date','Date'[Quarter Number]))
Var _Sum2=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Date'),[Quarter Number]=SELECTEDVALUE('Date'[Quarter Number])-1))
RETURN
DIVIDE(_Sum1-_Sum2,_Sum1)

Color measure = IF([Measure]>0,"Green","Red")

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.