What If Date Range Filter For Visual

[Anonymous]

Wondering if this is possible - been searching for a solution but no luck yet.  I have a requirement to allow users to select an end date, and then the number of weeks to look back from that selected date (using a What If parameter).  So if they select 12/27/20 and 8 weeks they would see totals similar to below.  I have a Week Ending date in my Fact table, and have also created a Calendar table to hold those unique Week Ending dates (is not currently connected to the Fact table).

 

It seems like, based on the examples I've seen,  it would work if I were to build the syntax into my existing measures (for example Dollar Sales) but this is an existing report with a lot of existing measures so it would be a pain in the $$ to to back and try and update them all.  So I'm trying to see if there is a way to create a filter that I can drop on to the visual(s) instead. I've managed to get it working correctly when the Week Ending date is in the visual by creating the following measure.

Date Filter =
VAR CurDate = MIN (PA2[Week Ending])
VAR SelectEnd = SELECTEDVALUE('Calendar'[Week Ending])
VAR SelectStart = SELECTEDVALUE('Calendar'[Week Ending]) - ('Weeks Shown'[Weeks Shown Value] * 7)
RETURN
IF( CurDate <= SelectEnd && CurDate > SelectStart, "Y","N")

However, when I remove the date from the visual the filter stops working (the Dollar Sales in the screenshot above without the date in it).  Is it possible to write syntax that would allow me to drop a filter onto a visual without having the Week Ending date on the visual?

Thx in advance for your assistance,

Jeff

[lbendlin · Accepted solution]

No, your What-if table needs to be a disconnected table, and as such will have no impact on the rest of the data model.  You'll have to do it the hard way and adjust your measures.

[Anonymous]

Got it - thx for the feedback.