Hi everyone,

I have a 2010 powerpivot workbook with a table of values showing the target days and actual days worked by employees by date Sep14 to Aug15.

I have two DAX measures which calculate running totals:

RunningTarget:=CALCULATE(SUM([Target]), FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date])))
RunningActual:=CALCULATE(SUM([Actual]), FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date])))

So, I have a pivot table which has a filter on the date column for YTD, giving me cumulative target and actual values by date up to and including today's date. The powerpivot table is refreshed from a datawarehouse that runs nightly, so there will never be any actual values against the current day. Today, for example, the table shows Target = 50, Actual = 0. I have a line chart running off the pivot with time on the x axis and the cumulative target and actual values increasing with time. My issues is that my graph gets skewed on today's date, with the cumulative actuals moving below the cumulative target due to the 0 value.

What I want is to be able to dynamically set a filter on the pivot for YTD excluding today's date. I could choose a date filter and set the pivot to include dates before today, but I'd then have to update the graph manually each day.

I expect I could also change the DAX formula to do it, but I'm not particularly competent with it, so hoping for easy pivot table focused solution.

Thanks anyone for any help.

Cheers
Tom