Adding constant from table as filter to date column

[kjohn10]

I want to provide a selectable "window" of days (from -10 to +10) to add to a date column. The window of days will be a slicer and the user can select one, which should update the column of dates day value by that amount. That date column is used in other calculations so we can see how moving the original date affects other values.

I used a simple query to create the window of day values, but getting the selected value added to the date column isn't working.

Simple example pbix here. How can I get that filter (slicer) to add the day(s)?

[v-huizhn-msft · Accepted solution]

Hi @kjohn10,

We can not create a dynamic calculated column based on slicer, please review this knowledge base. For your requirement, you can create a measure using the formula below rather than calculated column.

Adjusted Date_test =
DATEADD (
    FIRSTNONBLANK ( DateKey[Date], DateKey[Date] ),
    SELECTEDVALUE ( Query1[DayChange] ),
    DAY
)

Create a table visual, and select DateKey[Date], measure as value, you will get expected result as follows.

select 3 in slicerselect -1 in slicer
Please download attachment for more details.

Best Regards,
Angelia

[kjohn10]

@v-huizhn-msft Thank you for the KB link; I've replicated your solution, this should work, but will take me some time to link in the other calcs.