Value Lookup in Table with Effective Start and End Dates

DAX DAX Calculations
1

Hello, I am trying to find a % increase value with a start and stop effective date in a table for sales % increase goals. This value changes every year with a typical Jan 1 effective start date and Dec 31st end date. I pull prior year sales for that date and multiple by the rate taken from the lookup table where the desired date falls within the start and stop date range. The below measure works fine for prior dates, but does not return anything for future dates, including the current day. I think I am missing something simple but can’t seem to get it to work. Thanks for the help in advance.

>     Sales Goal %Inc = CALCULATE(  AVERAGE('Annual Sales %Inc Goals'[%Inc]), 
>                                     FILTER('Annual Sales %Inc Goals',  
>                                         SELECTEDVALUE(Dates[Date]) >= 'Annual Sales %Inc Goals'[StartDate]  
>                                         && SELECTEDVALUE(Dates[Date]) <= 'Annual Sales %Inc Goals'[EndDate]  
>                                     )
>                        )
2

First thing that pops into my head is: Does your date table have future dates?

3

Yes it does. I don’t have a relationship for either the start or end date to the date table though.

4

How are you doing any time intelligence? Are you using the under-the-hood date hierarchies that are created on each date column?

I’d recommend watching Sam’s video on budgeting/forecasting for insights in how to setup your model as well as the necessary formulas to make PBI do what you want it to do.

5

I found my issue, I had two tables with a both direction filter relationship that was causing the problem. Thanks for the help.

6

To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks