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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,155
Members
412,574
Latest member
shadowfighter666
Top