Dax measure for conditional formatting for matrix table on cells

[ChiRomeu]

Dear members,

 Please kindly help to solve the following problema:

I would like to have conditional formatting request in the matrix table: see the following picture

Green = 2

Red =1

Write=0

With color green:

If current month bookings > 0 and  last three months bookings = 0 then 2 (which means the cell of date (year-month) 2024-05 is green)

With color red:

Two requests:

No Regular red:

If current month and last month = 0 and -2 month and -3 month >0 then 1 (which means the cells of date (year-month) 2024-05 y 2024-04 are red)

Regular red:

If current month =0 and last month > 0 and -2 month and -3 month >0 then 1 (which means the cell of date (year-month) 2024-05 is red)

Otherwise:

write

Best regards

Chi

[v-kongfanf-msft]

Hi @ChiRomeu ,

You can try formula like below:

BookingStatus = 
VAR CurrentDate = SELECTEDVALUE(slicer[Date])
VAR CurrentBookings = CALCULATE(SUM(Bookings[Bookings]), Bookings[Date] = CurrentDate)
VAR LastMonth = CALCULATE(SUM(Bookings[Bookings]), DATEADD(Bookings[Date], -1, MONTH))
VAR TwoMonthsAgo = CALCULATE(SUM(Bookings[Bookings]), DATEADD(Bookings[Date], -2, MONTH))
VAR ThreeMonthsAgo = CALCULATE(SUM(Bookings[Bookings]), DATEADD(Bookings[Date], -3, MONTH))

RETURN
SWITCH(
    TRUE(),
    CurrentBookings > 0 && LastMonth = 0 && TwoMonthsAgo = 0 && ThreeMonthsAgo = 0, 2,
    CurrentBookings = 0 && LastMonth = 0 && TwoMonthsAgo > 0 && ThreeMonthsAgo > 0, 1,
    CurrentBookings = 0 && LastMonth > 0 && TwoMonthsAgo > 0 && ThreeMonthsAgo > 0, 1,
    0
)

Then create rule in conditional formatting:

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.

[ChiRomeu]

Hi Adamk  Kongs,

 Thank you for your help, but it is still not working, please see the following picture marked which violates the conditions put.

 beste regards