Filter a dataset based on another table Date rage

[AJBI]

i have the following offers table each offer has a start and an end date.

i have another dataset containing data about contracts each contract has a creation date

how can I make a dynamic slicer that filters the contracts data between the offer start and end date without connecting the two tables based on the offer name? 

[AJBI]

Hi Thank you for your response,

I have a question about how to apply the filtering.

the measure you wrote retrieves the total value of the contracts and when i try to apply slicers or filters from the second table based on the offer name nothing happens? 

here is a sample of my data

Contracts Table 

Id	CreationsDate	ContractValue
1	01/02/2022	100$
2	03/03/2022	250$
3	24/03/2022	364$
4	01/02/2022	255$
5	29/03/2022	100$
6	24/03/2022	250$
7	01/07/2022	364$
8	03/05/2022	255$
9	24/06/2022	100$

OfferTable

OfferName	Offerstart	OfferEnd
offer1	01/01/2022	02/02/2022
Offer2	03/07/2022	06/05/2022
Offer3	02/01/2023	07/08/2022
offer4	04/07/2023	08/11/2022
offer5	03/01/2024	09/02/2023
offer6	04/07/2024	13/05/2023
offer7	03/01/2025	14/08/2023

i want to know how to filter contracts based on offer start and end date.

thank you

[v-henryk-mstf · Accepted solution]

Hi @AJBI ,

Try formula like below:

M =
VAR sel_ =
    SELECTEDVALUE ( 'Table'[OfferName] )
VAR start_sel =
    CALCULATE ( MAX ( 'Table'[OfferStart] ), sel_ = 'Table'[OfferName] )
VAR end_sel =
    CALCULATE ( MAX ( 'Table'[OfferEnd] ), sel_ = 'Table'[OfferName] )
RETURN
    CALCULATE (
        SUM ( Data[Value] ),
        Data[OfferName] = sel_
            && MAX ( Data[Date] ) <= end_sel
            && MAX ( Data[Date] ) >= start_sel
    )

If the problem is still not resolved, please provide test data and expected result screenshoots. Looking forward to your reply.

Best Regards,
Henry

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