Conditional Formatting in Power BI

[PamWren]

Hi,

I have a matrix which shows apprentices and planned end dates, with amounts paid per month for training.  I want to highlight any payments made after the end date but I can't figure out how to do this.  Any help gratefully received.  Screenshot below:

So the top apprentice has an end date of Sept 2023 but a payment was made in October 2023, this is the figure I wish to highlight.

Many Thanks

[Anonymous]

Just realised I didn't mention how you use this measure, sorry! You will want to click on the matrix visual with your data then navigate to cell elements and select the value you want to apply this to:

Then turn on the conditional formatting you want to apply (I have chosen background in this case) and click the Fx button next to it, this should open this window for you:

Click the dropdown on Format Style and choose Field Value, then in the 'What Field should we base this on?' field, choose the measure you created (in my case it would be 'Conditional Format Value').

[Anonymous]

I made a sample power bi dataset based on how I think your data is structured, I have an employee table containing the employee name and the end date. I have another table called Payments which contains the employee; payment date and paid amount. These are linked on the employee name column. I believe the below meets your requirements:

The measure is effectively calculating the amount paid after the end date then returning a colour if the date in the table is larger than the end date and the paid amount after end date is not 0. Raw measure code:

Conditional Format Value =

VAR _end = SELECTEDVALUE(Employee[End Date])

VAR _check = CALCULATE(SUM(Payments[Paid Amount]), FILTER(ALL(Payments),

                                                        Payments[Employee] = SELECTEDVALUE(Employee[Employee])

                                                        && Payments[Payment Date] > _end

)

)

RETURN

IF(_check <> 0 && SELECTEDVALUE(Payments[Payment Date]) > _end, "Yellow" /*Put your colour here, can be a hexcode*/, BLANK())

Hope this helps, let me know if you have any issues or further questions.