Forum Discussion

ivan_abboud's avatar
ivan_abboud
Frequent Visitor
3 years ago
Solved

Conditional formatting in matrix based on growth between months

Hello community


I have a matrix with stores sales as rows and months as columns and I need to format the matrix cells based on the differences of sales between months, so if a store sales in Feb are higher than Jan, this cell should be colored with green, otherwise it should be red

The matrixRaw Data


I tried to apply solutions provided in this post:
Solved: Conditional formatting based on differences betwee... - Microsoft Power BI Community
but non of them worked for me

Thanks in advance

Need Help
  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    Hi ivan_abboud ,

    Assume you have a 'DateTable' :

     

    1-Create this calculated column in 'DateTable':

    YearMonthIndex = (DateTable[Year])*12+DateTable[Month]

     

    2-Assume Your measure Name is [Sales]=SUM('Table'[SalesAmount]) . Create the measure below:

    SalesPrevMonth=
CALCULATE (
    [Sales],
    FILTER (
        ALL ( 'DateTable' ),
        'DateTable'[YearmonthIndex]
            = MAX ( 'DateTable'[YearmonthIndex] ) - 1
    )
)

     

    3-Then Create the measure:

    SalesDiff=
[Sales]-[SalesPrevMonth]

     

    4-Then you can set conditional formatting by the [SalesDiff] Measure. (Greater and less than 0 condition on it)

     

    If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

    Regards,
    Loran

     

     

1 Reply

  • MohammadLoran25's avatar
    MohammadLoran25
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi ivan_abboud ,

    Assume you have a 'DateTable' :

     

    1-Create this calculated column in 'DateTable':

    YearMonthIndex = (DateTable[Year])*12+DateTable[Month]

     

    2-Assume Your measure Name is [Sales]=SUM('Table'[SalesAmount]) . Create the measure below:

    SalesPrevMonth=
CALCULATE (
    [Sales],
    FILTER (
        ALL ( 'DateTable' ),
        'DateTable'[YearmonthIndex]
            = MAX ( 'DateTable'[YearmonthIndex] ) - 1
    )
)

     

    3-Then Create the measure:

    SalesDiff=
[Sales]-[SalesPrevMonth]

     

    4-Then you can set conditional formatting by the [SalesDiff] Measure. (Greater and less than 0 condition on it)

     

    If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

    Regards,
    Loran