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 your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

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 your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

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!!!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 138 members 1,867 guests
Please welcome our newest community members: