Forum Discussion
Help with using all values from many-to-many relationship
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!
6 Replies
- lbendlin
Super User
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.