Slicer not filtering Cumulative Total

[Anonymous]

Hi, 

I have a dataset that looks like this:

Account/Donor	Amount	Source	Stage	Donor Decision Date
Donor	$1,299,998	HQ	Awarded	Monday, October 5, 2020
Foundation	$75,905	HQ	Awarded	Thursday, October 1, 2020
Person A	$4,032,428	HQ	Awarded	Wednesday, October 21, 2020
Donor	$12,598	HQ	Awarded	Tuesday, October 13, 2020
Donor	$50,000,000	HQ	Rejected	Friday, October 2, 2020
Donor	$2,999,890	HQ	Awarded	Friday, October 16, 2020
Person A	$632,119	HQ	Awarded	Wednesday, October 21, 2020
Foundation	$33,906	Field	Awarded	Thursday, October 15, 2020

I have a measure that returns the sum of amount that's been awarded:

FY21_Awarded = CALCULATE(
    sum(FY21[Amount]), FILTER(FY21, FY21[Stage] = "Awarded"))

I have another measure that returns the cumulative total of the amount awarded:

FY21_Cumulative Total = CALCULATE(
    FY21[FY21_Awarded], 
    FILTER(ALL(FY21), FY21[Donor Decision Date] <= MAX(FY21[Donor Decision Date])))

This all works great as anticipated:

 However, as soon I user the source slicer I get the following:

You'll see that the total returns $34k for the one record. However, the cumulative total is returning $1.42M. I understand that this is working as the measure intended and that on the date of the slicer selection of "Field" the cumulative total including the $34k is equal to $1.42M.

However, I would like the slicer to filter the cumulative total measure:

• If nothing is selected it would return the cumulative total for field and HQ. 
• If field or HQ is selected it would only return the cumulative total of awarded for either Field or HQ. 

I'm essentially looking for a dynamic cumulative measure based on the field and HQ slicer. 

Any information on how to proceed would be greatly appreciated.

thanks in advance!

[jdbuchanan71 · Accepted solution]

@Anonymous 

Chagne the ALL in your second measure to only affect the [Donor Cecision Date] coulumn:

FY21_Cumulative Total =
CALCULATE (
    [FY21_Awarded],
    FILTER (
        ALL ( FY21[Donor Decision Date] ),
        FY21[Donor Decision Date] <= MAX ( FY21[Donor Decision Date] )
    )
)

You also don't need the FILTER in your first measure, you can do it like so.

FY21_Awarded =
CALCULATE ( SUM ( FY21[Amount] ), FY21[Stage] = "Awarded" )

[Anonymous]

Thanks @jdbuchanan71!

That was... easy...