Transform Data to filter out records based on dates from another table

[Anonymous]

Hello,

I have a table with a large number of records which contains daily snapshots of each item (let's call this table "Historical").  I also have another table which contains two date fields which has a "Start Date" and an "End Date" column for every schedule (let's call this table "Range").

Sample data for Range table (the Start Date and End Date usually overlaps but there could be instances when they don't):

Schedule		Start Date		End Date
A		12/27/2023		1/10/2024
B		1/10/2024		1/24/2024
C		1/24/2024		2/7/2024
D		2/7/2024		2/21/2024

The "Historical" table contains a date column (let's just call it "Date") that indicates when the snapshot was taken.  I would like to filter out the records so that it only loads snapshots that were taken on the "Start Date" and "End Date" of the "Range" table and exclude all other daily snapshots so that I can reduce the data refresh time and decrease the table size.

It is possible to do this during Transform?

[Anonymous]

Hi @Anonymous 

For your question, here is the method I provided:

Here's some dummy data

“Range”

"Historical" 

Historical = CALENDAR("10/1/2023", "3/20/2024")

Create a measure. Search for dates in the "Range" range.

range_date = 
var _startDate = MIN('Range'[Start Date])
var _endDate = MAX('Range'[End Date])
RETURN 
    CALCULATE(
        SELECTEDVALUE('Historical'[Date]),
        FILTER(
            'Historical', 
            'Historical'[Date] >= _startDate 
            && 
            'Historical'[Date] <= _endDate
        )
    )

Here is the result.

Regards,

Nono Chen

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

[Anonymous]

Hi @Anonymous,

Thanks for your response.  Appreciate it a lot.  I am actually looking for a way to filter out the data during the load process so that all the records from Historical that does not match the Range table will not be loaded during data refresh.

Is there a way to do this?

[Anonymous]

Hi @Anonymous 

If you want to filter the data during the load data phase, you can go to the Power Query editor.

This allows you to filter the data before it enters the model.

You can set a date range in the Power Query editor to include only data from a specific time period, or exclude unwanted records based on certain criteria.

Select the columns you wish to filter and click on the drop down option to find Custom Filtering.

Regards,

Nono Chen

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

[Anonymous]

Thanks again @Anonymous but the Power Query filter just lets me input arbitrary dates and not based the value on another table column.  I am not sure if my scenario is even possible.  Thanks again!