All,
I need to a create time based aging filters (such as 30/60/90 days) in FM, based on 4 different dates provided in one dimension, such as Ship Date, Order Date, Release Date, and Entry Date (purely for example only). The aging is calculated based on the current date.
For example, Ship Date (30 days) = Ship Date between current date and (current date + 30). This implies the same for other dates as well.
Current requirement is to use the aging filter on the dashboard to dynamically filter the data based on the four dates.
There is a measure, that will be used on the dashboard to depict aging per the four dates. Thus, if a user selects 30 days, then the data will automatically, display the measure value for Ship date (30 days), Order Date (30 days), Release Date (30 days), an Entry Date (30 days). The issue is this needs to be done dynamically.
Currently, I created separate filter calculation in the FM model for each dates with different aging buckets. These are being as filter in the dashboard.
Is there a way to create an aging filter (with values 30/60/90) that can be used to filter data set for a measure based on the 4 dates respectively?
Thanks,
All -
rather than adding a filter, i tried creating a data item with case statement, that will add the respective age buckets for the 4 different dates based on the expression as listed above.
However, how will this data item work when used as a filter?
or can this one data item be used to filter the 4 dates respectively?
I am not sure I entirely follow.
Usually, for "aging buckets", I create a table with the buckets in the data warehouse, then link facts to the appropriate bucket (row) at ETL time. The aging buckets table can then be used as any dimension would for filtering. Assuming ETL is daily, I would think that would work here.
Thanks for the reply. I created a dummy aging table and was able to test the same.
However, the issue is that the dates are attributes of the dimension and not the fact table.
Thus, if I need to ensure that the aging table can be used to filter the 4 dates, then I will need to create an alias shortcut of the dimension 4 times, one for each date.