Combining two tables and filtering based on combined rows

[Tuna123]

I have two tables:

Date table		Data table			
Date		Product	Type	Start	End
1/2018		A	1	1/2019	1/2020
2/2018		A	2	1/2019	1/2021
3/2018		A	3	1/2020	1/2050
4/2018		B	1	1/2021	1/2022
5/2018		B	2	1/2022	1/2021
6/2018		B	3	1/2023	1/2021
7/2018		C	1	1/2018	1/2022
8/2018		C	2	1/2018	1/2022
9/2018		C	3	1/2021	1/2050
…		…	…	…	…

I'm trying to crossjoin the dates with product data  (product and type) into one table but I only need the date-product combinations when the product is "active" (between the start and end dates). Is there a way to do this?

[Zubair_Muhammad · Accepted solution]

Hi @Tuna123

This shall do it hopefully.

Add a new Table from Modelling Tab  as follows

CombinedTable =
GENERATE (
    'DataTable',
    FILTER (
        DateTable,
        DateTable[Date] >= 'DataTable'[Start]
            && DateTable[Date] <= 'DataTable'[End]
    )
)

[Tuna123]

Thanks @Zubair_Muhammad

This worked nicely.

[Zubair_Muhammad]

PBIX file based on your sample attached here