Get last year last entry

[ChristianDGreat]

I got stuck into this, hopefully, someone can help me with this
So I have this Custom Calendar Table

Season	Weekending	Product	Sub Product
2021	10/29/2022	A	A-1
2021	11/5/2022	A	A-1
2022	11/12/2022	A	A-1
2022	11/19/2022	A	A-1
2021	8/27/2022	A	A-2
2021	9/3/2022	A	A-2
2022	9/10/2022	A	A-2
2022	9/17/2022	A	A-2
2022	9/24/2022	A	A-2

and transacation table :

Season	Weekending	Product	Sub Product	Qty
2021	9/3/2022	A	A-2	111
2021	10/29/2022	A	A-1	222
2022	11/5/2022	A	A-2	33
2022	11/12/2022	A	A-2	44
2022	11/19/2022	A	A-1	55

The result Im looking for is something like this

Filter :
Season = 2022,
Product = A,
Sub Product = All

Product	Sub Product	Qty
A	A-1	0
A	A-2	111
Total		111

Explanation :

Basically what this does is if a user select Season = 2022, it need to scan the Custom Calendar on the MIN date available for 
Product, Season and Sub Product combination, in this case in row 1, its 11/12/2022, then we need to subtract 7 there
so the date will be 11/5/2022. Now we need to look at the transaction table if we have those match for the 2021 Season.
In this case theres none so its 0

For Row 2 result, the minimum date for the Custom Calendar is 9/10/2022, minus 7 thats 9/3/2022,
When we lookup the transaction table for 9/3/2022 and 2021 we get 111

Hope this makes sense.

*basically we are looking at the last weekending of the previous year.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Expected result measure: =
VAR _mindateandoneweekbefore =
    MIN ( 'Calendar'[Weekending] ) - 7
RETURN
    SUMX (
        FILTER (
            'Transaction',
            'Transaction'[Weekending] = _mindateandoneweekbefore
                && 'Transaction'[Product] = MAX ( 'Calendar'[Product] )
                && 'Transaction'[Sub Product] = MAX ( 'Calendar'[Sub Product] )
        ),
        'Transaction'[Qty]
    ) + 0