Dynamic Date Comparison in Power BI: Filtering and Joining Tables for User-Selected Dates

Hello Power BI Community,

I have a RawData table with the following columns:

• PO Number
• Line Number
• Need By Date
• Open Qty
• Date Extracted
  
  The data can have duplicate values

Goal:

I want to allow users to compare data for two selected dates using two dropdown slicers:

1. Date1: Created a table called First Date Filter Table, which contains all distinct dates from the Date Extracted column.
2. Date2: Created another table called Second Date Filter, which contains distinct dates from Date Extracted but excludes the latest date.

Expected Functionality:

• Based on the user-selected dates in the slicers, one table should show data filtered for Date1, and another table should show data filtered for Date2.
• Using a left join on PO Number and Line Number as the composite key, I want to compare the two datasets.
• After the join, I need to add comparison logic:
  • Need By Date: If the Need By Date matches for both dates, display "Dates Match", otherwise display "Dates Don't Match".
  • Open Qty: If the quantities match for both dates, display "No Change", otherwise display "Qty Change".

Issue:

I tried creating calculated tables, but calculated tables in Power BI do not dynamically respond to slicer selections. As a result, the data is not filtered based on the dates selected by the user in the slicers.

DAX formula's used for calculated tables that didn't work

Question:

What is the best way to achieve this dynamic comparison in Power BI? Any suggestions on how to filter and compare data dynamically based on user-selected dates would be greatly appreciated.

Thank you in advance for your help!

RawData

First Date Filter:

Second Date Filter: