Need Help with Filters on a running sum

[sloane]

Hi everyone, 

I have a table that looks something like this;

 and I want to be able to create a running total of the amount column and have two slicers controlling the output.

The first slicer is country which shows only the states in the country when triggered 

the second slicer (where the magic happens) is for state, and gives the sum for the respective state

My current formula (below) only returns the sum on a country level but not a state level

Amount Targeted = 
VAR endOfPeriod = MAX ( 'Calendar'[Date] )

VAR startOfPeriod = MIN( 'Calendar'[Date] )

RETURN
    CALCULATE (
        SUM(Table[amount]),
        FILTER (
            ALL(Table),
               ( Table[Date] <= endOfPeriod
                         
        )
    ))

 Thank you in advance for your help 🙂 

[amitchandak]

@sloane , Try like

Amount Targeted =

CALCULATE (
SUM(Table[amount]),
FILTER (
ALLSELECTED(Table),
( Table[Date] <= max(Table[Date])

)
))

[sloane]

Thanks but this still doesnt work when I filter the state slicer. It only works when I filter the country slicer

[v-kelly-msft · Accepted solution]

Hi @sloane ,

Create a measure as below:

Measure = IF(ISFILTERED('Table'[State]),
 CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[State]=SELECTEDVALUE('Table'[State]))),
 CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date]))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!