I have a PowerQuery questions I am hoping you can assist with. I have done some extensive research but cannot find an appropriate solution.
I have a dimension table that is time series and I want to use this table, based on the date to extract comparative lookup tables (i.e. Prior Qtr, Same Qtr Last year, Prior Yr End). The comparative dates could be linked to a current Quarter Parameter or be a new one.
Based on a reference date, (I.e. Quarter end date), I want to be able to add a calculated column that identifies which row is included for said date. This would include current product names and those which were previously discontinued. For discontinued products, I would expect to see the last submission based on the reference date.
I have preferenced doing this in the query editor so these can be unique tables that populate into the model although am open to suggestions as to best practice to achieve this.
I have attached a sample data set with a table you can import, and included explanations and expected outputs in the lookup tables.
Please let me know if you need more information or if I can be clearer.
PowerQuery Time Series Example Data.xlsx (12.1 KB)