cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbloomfield
Frequent Visitor

What If Date Range Filter For Visual

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).

 

Screen Shot 2021-08-25 at 2.12.40 PM.png

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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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.

View solution in original post

Got it - thx for the feedback.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.