Help with using all values from many-to-many relationship

[Anonymous]

Hello,

I have this model which connects parts to models and we are looking at cumulative sales (already calculated in data table)  of all models that are connected to each part. We have the part to model table as follows showing which part is used by which model.

With the help of this community, we were able to get the right totals on models for specific part as shown in the attached file. However, we realized that we are also interested in getting cumulative sales of models that were not sold in the selected date. As long as a model is sold in any of the last 10 years, we would like to consider it. So, in my output, we would also like to see M4 when we select 2022 for Part A01 as an example apart from M1 & M2 because A01 was used in Model M4 10 years ago. It would be true to include it if it was sold last year or any previous years until previous 10 years even if it was not sold in current chosen date.

PBI File: https://drive.google.com/file/d/15ProVCYds0H4peb6INQzHciay9fFxoek/view?usp=sharing

Anhy help is highly appreciated. 

Thanks!

[lbendlin]

I would propose a slightly slimmer data model

For any "not sold in a certain timeframe"  questions you need to use disconnected tables and/or cross joins.

[Anonymous]

Thanks @lbendlin!

Can you please show how we can see M4 as well in above example? That would be super helpful! Thank you!

[lbendlin]

For any "not sold in a certain timeframe"  questions you need to use disconnected tables and/or cross joins.

[Anonymous]

So, are you sayijng, I would need to create a separate table consisting of all models. I am still not able to figure this out. Can you please help on this small example that I have provided with required measures or queries. That would be helpful!

My real data is huge and trying to use above example for my purpose. 

[lbendlin]

You need a disconnected calendar table, not a disconnected products table.

[Anonymous]

I am not able to figure this out still. If someone can please help me achieve the output by solving this small sample data in .pbi file, it will be super helpful. 

I am thinking performing crossjoin on Date table & Model_Sales might work, but because both of these tables have date, I am not sure how to proceed with cross-join.

Thanks a bunch in advance!