Show remaining number of days in table based on date slicer

[EnzoRRS]

Hi! I'm trying to create a table in Power BI that shows all employees who are in their probation period, with the remaining number of days. For the sake of this example i have simplified the calculation that i'm doing: let's assume all employees have a probation period of 30 days after their employment date.

"Start dvb" = employment date

"Dagen resterend" = remaining days

Using the following DAX expression i created the remaining days column:

DATEDIFF(TODAY(), 'dim Resource Personeel'[Employment Date], DAY) + 30

So far so good. However, I now want to implement a forecasting feature. I want to give my users the option of selecting a future date, and then pretending that that future date is "today". So if an employee has 10 days probation left, and a user selects a day 5 days from now, then the table should show that employee as having 5 days probation left.

I have made a date slicer where users can select a single date. The selected date is returned with the expression

FIRSTDATE('dim Date Personeel'[DatesAfterToday])

So i replace TODAY() with that, giving me:

DATEDIFF(FIRSTDATE('dim Date Personeel'[DatesAfterToday]), 'dim Resource Personeel'[Employment Date], DAY) + 30

However, at this point i found out that calculated columns are not affected by slicers! So i'm back to square one. Is there any way to do this with a measure instead?

[tamerj1 · Accepted solution]

Hi @EnzoRRS 

please try this measure 

Measure1 =
DATEDIFF (
    MIN ( 'dim Date Personeel'[DatesAfterToday] ),
    SELECTEDVALUE ( 'dim Resource Personeel'[Employment Date] ),
    DAY
) + 30