cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ansa_naz Member
Member

Matrix - Create measure to see if a row date value is within the column year/month

Hi all

 

Is there any way to create a measure which compares the row value date with the matrix column Year/Month, and if the row date value is AFTER the column Year/Month then highlight it red?

 

See below current matrix, I am trying to highlight the background in red all cells where the maintenance completed date is AFTER the column Year/Month - these have been surounded with black borders:

 

Maintenance2.jpg

 

The Year/Month column values come from a Dates table, and the row date values come from my maintenance dataset.

 

Any ideas how I could do this? I have tried to use the LASTDATE function in DAX but it doesnt seem to do what I am looking for

 

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Matrix - Create measure to see if a row date value is within the column year/month

Hey,

 

you can create a measure like so:

background color =
var thiscalendardate = MAX('Calendar'[Date])
return
IF(MAX('maintenancetable'[date]) > thiscalendardate, "Red", BLANK())

Then you can use conditional formatting "background" by value on -the value/measure 

MAX('maintenancetable'[date])

Not sure if the value that represents the date in the matrix value is a measure, in this case you can use this measure instead of the MAX(...).

 

Hopefully, this is what you are looking for.

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
2 REPLIES 2
Super User
Super User

Re: Matrix - Create measure to see if a row date value is within the column year/month

Hey,

 

you can create a measure like so:

background color =
var thiscalendardate = MAX('Calendar'[Date])
return
IF(MAX('maintenancetable'[date]) > thiscalendardate, "Red", BLANK())

Then you can use conditional formatting "background" by value on -the value/measure 

MAX('maintenancetable'[date])

Not sure if the value that represents the date in the matrix value is a measure, in this case you can use this measure instead of the MAX(...).

 

Hopefully, this is what you are looking for.

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
ansa_naz Member
Member

Re: Matrix - Create measure to see if a row date value is within the column year/month

Hi @TomMartens

Thanks for the reply, thats brill!!!