Date Filters

[rcarhart2]

I am trying to get a massive switch statement to filter dates dynamically and I do not know if this is possible. I have three main date calculations. CurrentDatePeriod,PriorDatePeriod, and AsOfDate. CurrentDatePeriod and PriorDatePeriod are booleans which look at the date comparison value and either say true or false depending if the calender date is in the selected time frame. 
For example, the value last month vs prior month has been chosen. The AsOfDate shows that 11/2/2024 is the end of the month.

This logic works fine but i run into some issue when trying to get it to work with other data tables. 
First issue: I would like to be able to place the measure: currentDatePeriod or PriorDatePeriod on the filter view and set it to true or false but PowerBI does not seem to be able to handle this. 
Second Issue: How can I get this logic to work with my fact sales table so I can create calculations such as 

total_customers_CP = IF([currentDatePeriod],[total_customers],BLANK()). My sales table is not reacting to the date filter calculations. 

 Data model:

Do I need to make the date comparison table a parameter then add that to my currentdateperiod,priordateperiod,and asofdate calculations. 

let me know if you need further advice. I have pasted a link to an example workbook for anyone who wants to try and solve the problem. Greatly appreciate the help.PowerBI Date Logic 

[johnt75]

Have a look at https://www.sqlbi.com/articles/using-calculation-groups-or-many-to-many-relationships-for-time-intel... for some different ways you can tackle this.

[rcarhart2 · Accepted solution]

@johnt75 that article was very insightful. Would you have any idea how to apply that logic to my already made calculations? If that is not possible, would I have to delete my calculations and rebuild it based on the logic in that article. One part of the article that is unclear is: 

how is the Period table related to the date table? specifically this part: The resulting table needs to be linked to Date through a bidirectional relationship so that a filter on Period is propagated first to Date, and then to Sales.

[johnt75]

I think you would probably have to redo your calculations to take account of how you're setting up the periods, either calculation groups or as a table.

You wouldn't necessarily need to use the config table method that SQLBI proposed, you could build the table in any way you wanted, perhaps a combination of GENERATETABLE, DATESBETWEEN and UNION could construct what you need.

The relationship from Period to Date needs to be bidirectional because the relationship is many-to-one from Period to Date, and a single direction relationship would mean that Date filters Period. You need to be able to place a filter on Period, e.g. using a slicer, and have Period filter Date.