Matrix with conditional formatting does not work if I don't add the measure on the Matrix

[MarceloDF]

Hi, I have this Matrix:

		Jan-23	Feb-23	Mar-23	Apr-23
Customer1	Product1	2	2
	Product2		2	2
Customer2	Product3	3		3

The numbers are simple measure Total Quantity = SUM('FactTable'[Quantity]) but I added the condition that if it is 0, show me blank().

What I want to do is a measure that paint the cells that have this condition:

If "This Month" is blank AND "The Previous Month" is greater than 0 AND "The previous previous month" is greater than 0.

I tried to do it like this but didn't work:

Color Flag = 

IF (  (  ([Total Quantity] ) ) = BLANK()

         &&   ([Total Quantity Previous Month])   > 0

        &&   ([Total Quantity Previous Previous Month]) > 0

, "1" , BLANK() )

Also, I tried with CALCULATE in each measure to give them context but does not work.

If I put this Color Flag measure in the Matrix, it works, so I don't know what I'm doing wrong.

Thank you in advance for the help.

[MarceloDF]

Hi @Raghava2422 

We have LAG in DAX? I don't find it.

How can I use LAG in the measures?

Thank you

[Raghava2422]

Hi @MarceloDF 
create two measures for "Total Quantity Previous Month" and "Total Quantity Previous Previous Month" using the 'LAG' function to get the values of the previous months:
Total Quantity Previous Month =
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Date])
VAR PreviousMonth = CALCULATE(MAX('Calendar'[Date]), 'Calendar'[Date] < CurrentMonth)
RETURN
IF(ISBLANK(PreviousMonth), BLANK(), [Total Quantity])

Total Quantity Previous Previous Month =
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Date])
VAR TwoMonthsAgo = CALCULATE(MAX('Calendar'[Date]), 'Calendar'[Date] < CurrentMonth - 1)
RETURN
IF(ISBLANK(TwoMonthsAgo), BLANK(), [Total Quantity])

you can create your "Color Flag" measure using these new measures and your desired logic:
Color Flag =
IF(
ISBLANK([Total Quantity]) &&
[Total Quantity Previous Month] > 0 &&
[Total Quantity Previous Previous Month] > 0,
1,
BLANK()
)

"Color Flag" measure should now correctly evaluate the condition you described. It checks if "This Month" is blank, "Total Quantity Previous Month" is greater than 0, and "Total Quantity Previous Previous Month" is greater than 0. If all these conditions are met, it returns 1; otherwise, it returns BLANK().

When you place the "Color Flag" measure in your matrix, it should highlight the cells that meet your specified criteria.