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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,113,908
Messages
5,544,989
Members
410,647
Latest member
LegenDSlayeR
Top