Dynamic Conditional Formatting Based On Previous Year

[lukinfo]

Hi,

I don't know if this is possible, if so, how to do it? If last year's value is greater than next year's, I would like to change the next year's color to red. Just like in the attached screenshot. Please help. Thank you in advance!

my date

what I want to get

[ChiragGarg2512 · Accepted solution]

@lukinfo Try this measure:

color =

VAR _prev = CALCULATE([VALUE], PREVIOUSYEAR('Table (3)'[Year]))

var _curr = [VALUE]

return

SWITCH(TRUE(), _prev>_curr, "Red", "Green")

[Alex87 · Accepted solution]

Hello,

You can achieve this using the following DAX measure

CF_FontValue = 

VAR _CurrentYearResult = SUM(MyTable[Value])
VAR _PrevPer = SELECTEDVALUE(Dates[CurrYearOffset]) -1
VAR _PreviousYearResult = 
        CALCULATE(
        SUM(MyTable[Value]), 
              FILTER(ALL(Dates),
               Dates[CurrYearOffset] = _PrevPer)
               )
VAR _Result = IF(_CurrentYearResult < _PreviousYearResult, "red", "black")

RETURN
_Result

On your matrix visual, right click on values measures and select Conditional Formatting / Font Color. Format style should be Field value based on the CF_FontValue measure. Apply on values and totals

If it answers your need, please mark my reply as the solution. Thanks!

P.S If you are looking for the Dates calendar talble I used, you can find it here: 

Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

[Alex87]

you are not following the solution steps proposed. You do not have a date table in your model.  Add the date table as I recommend in my solution, adapt the formula accordingly and get rid of the Date hieararchy, and it will work.

[Alex87 · Accepted solution]

Hello,

You can achieve this using the following DAX measure

CF_FontValue = 

VAR _CurrentYearResult = SUM(MyTable[Value])
VAR _PrevPer = SELECTEDVALUE(Dates[CurrYearOffset]) -1
VAR _PreviousYearResult = 
        CALCULATE(
        SUM(MyTable[Value]), 
              FILTER(ALL(Dates),
               Dates[CurrYearOffset] = _PrevPer)
               )
VAR _Result = IF(_CurrentYearResult < _PreviousYearResult, "red", "black")

RETURN
_Result

On your matrix visual, right click on values measures and select Conditional Formatting / Font Color. Format style should be Field value based on the CF_FontValue measure. Apply on values and totals

If it answers your need, please mark my reply as the solution. Thanks!

P.S If you are looking for the Dates calendar talble I used, you can find it here: 

Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

[ChiragGarg2512 · Accepted solution]

@lukinfo Try this measure:

color =

VAR _prev = CALCULATE([VALUE], PREVIOUSYEAR('Table (3)'[Year]))

var _curr = [VALUE]

return

SWITCH(TRUE(), _prev>_curr, "Red", "Green")

[lukinfo]

 This is my file. What am I doing wrong?

Conditional Formatting 1 

[v-jianpeng-msft · Accepted solution]

Thanks solutions from @Alex87 and @ChiragGarg2512 , your solutions is great.

Hi, @lukinfo 

Based on the PBIX file you provided, I created a measure using the following DAX expression:

MEASURE =
VAR _curYear =
    SELECTEDVALUE ( Tabela[Date].[Rok] )
VAR _curValue =
    CALCULATE ( SUM ( Tabela[Value] ) )
VAR _lastyear =
    CALCULATE (
        SUM ( Tabela[Value] ),
        FILTER (
            ALLEXCEPT ( 'Tabela', 'Tabela'[Region] ),
            YEAR ( 'Tabela'[Date] ) = _curYear - 1
        )
    )
RETURN
    IF ( _lastyear > _curValue, "red" )

Using this metric in cell elements, the result is as follows:

I uploaded the PBIX file that I used this time.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

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.