DAX Help/Weird Problem - Measures, MAX (using dates) and Conditional Formatting

[pdmU100]

Hi,

I wonder if you clever people can please help me..!

I'm trying to conditionally filter a row depending on if a row value matches the max value of that column. The conditioning is rule based. 1 = Colour the cell, 0 = don't colour.

Apologies for the awkawrd screen cuts below but I needed to withhold some columns data.

This works;

However I don't want the date hard coded as I'd then need to amend it each month, so I made a measure 'Current BDX Month' to return the 'max' date value in the source, which it does;

But when I combine the two, the formatting applies to everything..? What am I missing? I've tried playing with the column formatting, I've tried using MAXA, I've tried forcing the values to DATEVALUE or just VALUE, I've tried using a variables and nothing seems to work, it just applies the conditioning to all the rows..;

Even if I move the MAX measure into the conditioning measure it still doesn't work;

Just for the avoidance of doubt; all values in the reporting period column are dates and they all begin on day 01, its the months and years that change (i.e., 01 Nov 2021, 01 Dec 2021, 01 Jan 2022 etc).

Thanks for your help.

[johnt75]

I think when you're calculating the max value for the date it is doing that within a row context which is filtering the date to just that month, so the max value will always equal the current value. You need to remove the filters when you're doing the calculation, so

Current BDX Conditioning =
var maxDate = CALCULATE( 
    MAX( 'ALL'[Reporting Period (Start Date)], 
    REMOVEFILTERS( 'ALL'[Reporting Period (Start Date)]
)
RETURN IF ( SELECTEDVALUE( 'ALL'[Reporting Period (Start Date)]) >= maxDate, 1, 0 )

[pdmU100]

Hi John,

Thanks, thats an  interesting thought. Never considerd their might be inherited filers on the rows already, unfortunately it doesn't seem to work! 

I'll keep playing about with it.

[johnt75 · Accepted solution]

There may be filters applied to other columns than just the [Reporting Period (Start Date)] column. Try removing that column from the REMOVEFILTERS so you're just left with REMOVEFILTERS('ALL')

[pdmU100]

Perfect - did the job - thanks so much 🙂