Will really appreciate your help in this, I have a request to built a dashboard on inventory management and I have created and attached a hypothetical scenario workbook for your reference.
There is an issue which I am currently facing which I need your advise when possible. Currently, I have access to two data sources ("Main" data source and "Ending Date" data source).
Every product code has an ending date and they would like to create 3 categories based on the date difference between the "Reporting Date" parameter and the ending date in secondary data source.
"Current" : How many inventory units? [when ending date is more than reporting date]
"Previous -1" : How many inventory units? [when ending date difference with reporting date is less than or equal to 7 months]
"Previous -2" : How many inventory units? [when ending date difference with reporting date is more than 7 months]
The dashboard will have a filter is when the user selects the fruit store (oranges, lemon or papaya).
Here is the situation, the ending date for the product code is from another data source and its different based on which country the fruit store is located. Another requirement is that the ending date has to be the latest date if there are multiple dates for the product code.
Is possible to derive a logic such that when the user filters to specific fruit store , it will determine the product code's latest ending date based on its country and categorize them?
I used a fixed LOD to derive the latest ending date based on the product code, but this will not work on a blended data source, since both the "country" and "product code" field needs to be blended for it to give an accurate ending date.