Solved: Conditional Formatting for MoM Growth with Arrows & Multi-Year Filtering

[Balqis_Adan]

 

Hi everyone,

I wanted to share how I solved a formatting and filtering challenge with MoM Growth and Arrows.

The Problem: When I combined the Growth  and the Arrow in one measure, Power BI treated it as Text. This made it impossible to apply conditional formatting Green,Red colors

My Solution Step-by-Step

1. Splitting the Measures: I created one measure for the calculation and a separate one for the visual indicator. This allowed me to keep the numeric value for formatting while still showing the arrow.

2. Handling Multi-Year Filters  I had an issue where filters didn't work correctly across multiple years. I solved this by integrating MoM and YoY logic together, ensuring the calculation stays dynamic regardless of the year selected

3.  the logic i use

MoM Growth =
VAR CurrentSales = [Total Revenue]
VAR LastMonth = CALCULATE([Total Revenue], DATEADD('Cleaned_Hospital_Data'[Admission_Date].[Date], -1, MONTH))
VAR MoM_Val = DIVIDE(CurrentSales - LastMonth, LastMonth, 0)


RETURN
    MoM_Val

MoM Growth with Arrow =
-- 1. Xisaabinta MoM (Month-over-Month)
VAR CurrentSales = [Total Revenue]
VAR LastMonth = CALCULATE([Total Revenue], DATEADD('Cleaned_Hospital_Data'[Admission_Date].[Date], -1, MONTH))
VAR MoM_Val = DIVIDE(CurrentSales - LastMonth, LastMonth, 0)
VAR MoM_Arrow = IF(MoM_Val > 0, "▲ ", IF(MoM_Val < 0, "▼ ", "▬ "))
-- 2. Xisaabinta YoY (Year-over-Year)
VAR LastYear = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Cleaned_Hospital_Data'[Admission_Date].[Date]))
VAR YoY_Val = DIVIDE(CurrentSales - LastYear, LastYear, 0)
VAR YoY_Arrow = IF(YoY_Val > 0, "▲ ", IF(YoY_Val < 0, "▼ ", "▬ "))
-- 3. Natiijada oo isku dhafan
RETURN
    FORMAT(MoM_Val, "0%") & " " & MoM_Arrow

[lbendlin · Accepted solution]

What is your question?  Are you looking for the blog section?

[lbendlin · Accepted solution]

What is your question?  Are you looking for the blog section?

[Balqis_Adan]

Hi lbendlin, thanks for the reply! I was sharing a solution I found for a formatting issue I had. As a beginner, I thought this might help others facing the same problem. Is the blog section better for sharing solutions even if they are simple? Thanks

[lutz_bendlin · Accepted solution]

Yes, it is a better place, and your content won't drown as quickly as in the regular forums.