Forum Discussion
Dynamic table for custom date periods
Your "Date Period Filter" table is a calculated table. It has no idea what you mean by SELECTEDVALUE, and thus is ineffective. No need to have it in your data model at all. Use measures to grab the selected value of the Date Period options and then dynamically apply that filter to the results.
Your "Date Period" table would only work if you use Import mode and refresh your semantic model daily. Otherwise "TODAY()" becomes meaningless.
Your main table visual needs to source the date from the Calendar dimension, not from the Sales fact. You will also want to normalize the country, region and item dimensions out of the fact table.
You are right, the 'Date' table is a dimension model.
I have attached an example .pbix in to this reply.
Sales_Custom_Time_Period.pbix
Your "Date Period Filter" table is a calculated table. It has no idea what you mean by SELECTEDVALUE, and thus is ineffective. No need to have it in your data model at all. Use measures to grab the selected value of the Date Period options and then dynamically apply that filter to the results.
Your "Date Period" table would only work if you use Import mode and refresh your semantic model daily. Otherwise "TODAY()" becomes meaningless.
Your main table visual needs to source the date from the Calendar dimension, not from the Sales fact. You will also want to normalize the country, region and item dimensions out of the fact table.