How to connect this table to existing table and to be used as a filter?

[balaganeshmohan]

I have a modelling problem, my current set up is standard in the sense that my date table and item(products) filters sales table (along with many many other tables) which is on the many side and all my calculations are written this way, Now I have a major task in that I have to integrate the status of these products which comes from the lifecycle table sourced from Odata.

These status have 5 categories and each one has a start date and end date attached to it. So the format of lifecycle table is ITEM|START|END|STATUS. The expectation is that the filtering in reports should work on status like a timeline, like if someone selects 2019, then they should see the report with items and their corresponding statuses from the 2019 POV. This should offcourse affect all the measure and calculations within that report as well.

From my understanding, lets say someone wants to look at net revenue for items with status "INTRODUCTION" in year 2023, then it has to filter on date first and status or status first and date and then calculate net revenue based on the filter context. My sales table for example is on daily level.

My current implementation is that I transform my lifecycle table into one row per day, per item, per status but this will result in a big table that is placed in between date and sales, but this is not intuitive and honestly bad because it creates millions of record. Will there be a better way to achieve this? If this is not the best description, I can provide more details if someone wants. TIA

[Idrissshatila]

Hello @balaganeshmohan ,

try linking the item and calendar table to the lifecycle table and thus you'll see the status per item per date.

[balaganeshmohan]

I only have date from and to columns in lifecycle, connected to date table doesn't make it work when performing calculations.

[Idrissshatila]

@balaganeshmohan , but how would you know the status of the item if there's no item column that indicates that this status refers to this item ?

[balaganeshmohan]

Sorry I wasn't clear.

1. I can make a connection to item table with the item key in lifecycle. This will be help in filtering with statuses, but still this will be many to many when it reaches sales as there are multiple statuses over the years for any single given item and this is indicated by start and end date.
2. I cannot connect to date table without transforming this lifecycle into daily(creates 35M records for all items). I want to avoid this.