Re: Combining Filter function? Running Total

Teodor_TRU · ‎01-31-2017

Context:
Building a burndown chart with a list of tasks based on their due dates. These snags have certain ID's so I have to count them.
I am trying to build a baseline (a line to show predicted completion of tasks) and have only been able to link them to their due dates (x axis)

This greatly skewes my graph as I do not have consistent due dates.

I tried creating a new table with dates and creating a relationship between all the dates and the due dates. When I do this my cumulative curve goes awol.

Bassicallly what im trying to do is go from this:

RunningTotal = 
CALCULATE    ([Count of Tasks],
FILTER ( ALL ( task_summary[Due Date] ), task_summary[Due Date] <= MAX ( task_summary[Due Date] )))

To a combination of these two.

RunningTotal = 
CALCULATE (
[Count of Tasks],
FILTER ( ALL ( '2017' ), '2017'[Date] <= MAX ( '2017'[Date] ) ))

++++++++

CALCULATE (   
[Count of Tasks],
FILTER ( ALL ( task_summary[Due Date] ), task_summary[Due Date] <= MAX ( task_summary[Due Date] )))

Is there a way to combine the filter ?
Any other way to do this?

Anonymous · ‎02-01-2017

Hi @Teodor_TRU,

If these tables contain the relationships, you can try to directly add the filter to current formula:

RunningTotal = 
CALCULATE (   
[Count of Tasks],
FILTER ( ALL ( task_summary[Due Date] ), task_summary[Due Date] <= MAX ( task_summary[Due Date] )),
FILTER ( ALL ( '2017' ), '2017'[Date] <= MAX ( '2017'[Date] ) ))

If they not contain the relationship, you should add the relationship first.(e.g. create a relationship between these tables and calendar table).

Regards,
Xiaoxin Sheng

Teodor_TRU · ‎02-02-2017

If only it was that simple :P.

I tried that already and it doesn't work. When I used what you just mentioned, it doesn't stay cumulative. It just dips whenever a certain task goes past its due date but then the baseline returns back to its maximum. I want the changes in it to be permanent.