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

[Hashmeet]

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

Filtered_RawData_First =

CALCULATETABLE(

        RawData,

        RawData[Date Extracted] IN VALUES('First Date Filter'[Date Extracted])

)

Filtered_RawData_Second =

CALCULATETABLE(

    Raw_Data,

    USERELATIONSHIP('Second Date Filter'[Date Extracted], RawData[Date Extracted]),

    RawData[Date Extracted] IN VALUES('Second Date Filter'[Date Extracted])

)

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:

[Anonymous]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @Hashmeet 

As you have discovered, slicers cannot directly influence the generation of calculated tables and calculated columns.

Below are the screenshots of the relevant answers:

For more details, please refer to:

Solved: Using Slicer values in a calculated column - Microsoft Fabric Community
Solved: Calculated column based on slicer value - Microsoft Fabric Community

Solved: Filter temporal table with filter value - Microsoft Fabric Community
 

Therefore, I suggest using the SUMMARIZE() function or the SUMMARIZECOLUMNS() function within a measure to generate the desired results based on your final expectations.

For more details, please refer to:

SUMMARIZE function (DAX) - DAX | Microsoft Learn

SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Learn
Table manipulation functions (DAX) - DAX | Microsoft Learn

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What do you need the slicers for? Put the calendar date column in the columns area of your matrix visual and teach your users how to use the filter pane to select two dates.