Enable filters to affect a cumulative measure

IR15H

New Member
Joined
Jun 8, 2013
Messages
10
Hi,


I've created various measures to help try and show a baseline plan of implementation versus actual, which works as expected when there's no filters applied. However, when I apply a filter, none of the measures update and instead continue to show the full, 'unfiltered', values. I have tried googling for a few days and reading similar examples but just can't crack it on my own.

I created a new calendar table within Power BI based on the earliest/minimum 'base date' and the latest/maximum 'plan date', called "Common Date".

My measures are;

Code:
xLIVE = CALCULATE (
    SUM( Sheet1[Value] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Plan Date] <= MAX ( 'Common Date'[Date] ) && Sheet1[Status] = "Live"
    )
)

Code:
xbaseplan =
CALCULATE (
    SUM( Sheet1[Value] ),
    FILTER ( ALL ( Sheet1 ), Sheet1[Base Date] <= MAX ( 'Common Date'[Date]  ) && Sheet1[Base Date] <> BLANK ()  ))

Code:
xr Revised Plan = CALCULATE (
    SUM( Sheet1[Value] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Plan Date] <= MAX ( 'Common Date'[Date] ) && Sheet1[Plan Date] <> BLANK ()
    )
)

An example where it doesn't work, displaying 'all' data, even when filtered;



I've also linked and an example file for convenience (can't upload Power BI files on here).


Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,118,281
Messages
5,571,290
Members
412,376
Latest member
osborne
Top