Dynamic table for custom date periods

[tolgalogy]

The core fact table in my data model is the 'Date' table. I would like to add a slicer that includes custom date periods such as YTD, current month, previous month, today, yesterday, etc., to the previously generated calculated measures and visuals.

So, I created a 'Date Period' table that includes [Period Name], [Start Date], and [End Date].

Date Period = {
("Today", TODAY(), TODAY(), 1),
("Yesterday", TODAY() - 1, TODAY() - 1, 2),
("Current Month", EOMONTH(TODAY(), -1) + 1, EOMONTH(TODAY(), 0), 3),
("Previous Month", EOMONTH(TODAY(), -2) + 1, EOMONTH(TODAY(), -1), 4)
}

I want to limit the 'Date' table according to the chosen value of 'Period Name' in a slicer and create a dynamic 'Date Period Filter' table that includes all the date values between [Start Date] and [End Date] in the [Dates] column.

Date Period Filter =
VAR SelectedPeriod = SELECTEDVALUE('Date Period'[Period Name])
VAR MinDate = CALCULATE(MIN('Date Period'[Min Date]), 'Date Period'[Period Name] = SelectedPeriod)
VAR MaxDate = CALCULATE(MAX('Date Period'[Max Date]), 'Date Period'[Period Name] = SelectedPeriod)
RETURN
CALENDAR(MinDate, MaxDate)

I was planning to create a relationship between the 'Date Period Filter' table and the 'Date' table to automatically filter the 'Date' table with the dynamically calculated 'Date Period Filter' according to the chosen 'Period Name' value in a slicer.

However, the 'Date Period Filter' comes up empty even though a period is chosen in the slicer.

Why is this happening?

[tolgalogy]

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 

[lbendlin · Accepted solution]

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.

[lbendlin]

The core fact table in my data model is the 'Date' table.

You meant to say the core dimension table.

Can you show your data model?