Filtering using date table not working how I would like

[brokencornets]

Hi all

I have a list of tasks and with a frequency (in days) and an initial start date.

What I want to do is use a date filter to be able to show how many iterations needed to be completed within a given timeframe. I've written the following calculated column and it ALMOST works, except it appears to complete ignore the date filter:

Expected Iterations =

VAR maxdate= max('Date'[Date])

VAR mindate=max(min('Date'[Date]),'Task List'[First Instance])

RETURN

if(mindate>maxdate,0,

CALCULATE(datediff(mindate,maxdate,DAY),USERELATIONSHIP('Date'[Date],'Task List'[First Instance]))/'Task List'[Freq Days])

So I get results showing like the below - rather than show total iterations in the 12 month period, it shows total iterations due from First Instance (or Min of Date table) to Max of Date table (today + 10 yrs).

If a task has a frequency of 365 it should have expected iterations of 1 based on the 12 month date range - while those with Freq 180 should show 2, Freq 28 maybe 12 or 13, etc.

I'm sure it'll be due to relationships but struggling to get my head around it! Any help much appreciated.

 

[johnt75 · Accepted solution]

Filters and slicers don't affect calculated columns, they are only calculated once when the data is refreshed.

If you need the values to react to to slicers try doing it as a measure instead.

[brokencornets]

Thanks both, I now have a working measure!

[AlexisOlson · Accepted solution]

It looks like you might have defined this as a calculated column rather than a measure and calculated columns cannot be responsive to slicers since they are computed only when the data model is loaded or refreshed, not in response to user interactions or report settings like measures are.

Edit: @brokencornets, it looks like @johnt75 saw the same thing first so please mark their answer as the solution if you choose between us.

[johnt75 · Accepted solution]

Filters and slicers don't affect calculated columns, they are only calculated once when the data is refreshed.

If you need the values to react to to slicers try doing it as a measure instead.