How to highlight unmatching data based on date fields from different tables

[Alizee]

Hello,

I have two different tables : Placements and Invoices and basically, the goal of the report is to highlight placements that does not have an invoice on a given month despite the fact that the month in question is between the start and end date of the placement.

For example, in the below are displayed data from the two tables. Both are connected through the Placement ID (Item Code in the Invoice table).

The right table shows the placements ID and matching invoices dates for February and the left table shows all placements that should technically be in it since the start date is before February and the end date after.

I don't know how I should do to highlight the following in a conditional formating way:
- If the chosen month is between the Start Date and End Date of a given placement and there is an Invoice Date for this placement (meaning there has been an invoice for the month), then GREEN.

- If the chosen month is between the Start Date and End Date of a given placement BUT there is no Invoice Date for this placement (meaning there has not been an invoice for the month), then RED.

- If there is an invoice Date for a given placement but the invoice date is not within the start date and end date of the placement, then ORANGE.

Thank you for your help!

[v-jingzhang · Accepted solution]

Hi @Alizee 

You may first download the attached PBIX to see whether this is close to what you want. 

I created two measures in the report. The first measure is to play as a visual-level filter on the left table so the table will only show placements whose date begin dates are earlier than the selected month and date end dates are after the selected month. 

Date Flag = IF(SELECTEDVALUE('Placement Table'[Date Begin])<MIN('Date'[Date])&&SELECTEDVALUE('Placement Table'[Date End])>MAX('Date'[Date]),1,0)

The second measure is to decide colors for different scenarios. I didn't consider the scenario with ORANGE color as currently the left table show placements whose (start date ~ end date) range can cover the selected month. As a result the placements that meet the third condition will not show. 

Color = 
VAR _itemCodes = VALUES('Invoice Table'[Item Code])
RETURN
SWITCH(TRUE(),SELECTEDVALUE('Placement Table'[Placement ID]) IN _itemCodes, "GREEN","RED")

If you want to show the third condition placements in the left table, I'll remove the first measure filter on it and modify the color measure. Let me know your expected result. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Alizee]

Hello @v-jingzhang ,

Thank you so much for your reply, it really was the solution I was looking for in terms of the first part of the problem!

As for the ORANGE color code, it is a filter I would actually like to add to the right table (the invoice table) to highlight the invoice dates for placements that are not within the right time range.

E.g, if I am selecting February 2022, thanks to your filters, the left table will highlight all placements within the range and color-filter them based on if they have an invoice or not. But I would also like to highlight in the right table in orange the invoice date/placement id that do not match any of the placements shown for february. Do you know how I could do it? Would it be through a conditional formating measure as well?

Thank you again for your help, it really cleared the issue for me/

Kind Regards,

Alizée