How to prevent running total from being recalculated when using a year slicer

[Anonymous]

Hey everyone,

First time working with PowerBI and having a bit of trouble with understanding the slicers and running total.

I am trying to track equipment deployment over months and wanted to compare them year to year.

I needed the running total to not reset each year as we need to know that 10 units were deployed in December 2010 and then +1 (11) in January 2011.

I'm also trying to show this in a line chart with the series being each year.

However, I also have another splicer for Company which will need to recalculate the running total if a specific company is selected.

(To add to the complication, I have another slicer for products but we can ignore that for now)

Ex)

I'm using the year slicer as a way to filter out the other series / lines.

How do I go about setting the running total to only be affected by the company slicer and not the year slicer?

Formulas I have tried:

Attempt 1:

NetQuantityRunningTotal =

CALCULATE(

SUM('transaction_records'[net_quantity]),

FILTER(

ALLSELECTED('transaction_records')),

'transaction_records'[transaction_date] <= MAX('Date'[Date])

)

)

The running total starts off based on the start date of the date slicer. Causing my chart to go into the negatives if items were returned. 

Attempt 2:

CALCULATE(

SUM('transaction_records'[net_quantity]),

FILTER(

ALL('transaction_records'),

'transaction_records'[transaction_date] <= MAX('Date'[Date]))

)

Company slicer no longer affects the running total

I also saw there was ALLEXCEPT but I could not get them to work. The Date slicer is using 'Date'[Date].[Year] as its options.

Is there a way to have the running total calculate based on select filters on the page but have the other filter only modify what years are shown?

[Anonymous]

Hi I  am using a running 12 month total measure like below

TTM_Income =

var currentDate = max('EOM Date'[End of the month])

var previousdate = date(year(currentDate),month(currentDate)-12,day(currentDate))

var result= calculate( sum('Raffle Transaction'[amount]), filter('Raffle Transaction','Raffle Transaction'[End of the month]>=previousdate && 'Raffle Transaction'[End of the month]<=currentDate))

Return

result

where 'EOM Date' is a date table disconnected from the actual table "Raffle Transcation". I need to group the values of the measure in  bands to show the count such as 0-£50,£50-£100,£100+ etc. if £100+ then show the ID. I tried to create a summarize table and band the measure TTM_Income there but It didnt work. Is it because of the diconnected table 'EOM Date' ?

[Greg_Deckler]

Well, seems like ALLEXCEPT should be your path to victory, what problem were you having? You would want to use something like ALLEXCEPT('Table'[Company])

[Anonymous]

Thanks for the quick response!

So my 'contractor name' is part of another table, so when I use the ALLEXCEPT and provide the contractor table, the conditional line that comes after cannot find 'transaction_date'.

NetQuantityRunningTotal =
CALCULATE(
SUM('transactionrecord'[net_quantity]),
FILTER(
ALLEXCEPT('contractor', 'contractor'[Contractor Name]),
'transactionrecord'[transaction_date] <= MAX('Date'[Date])
)
)

[Anonymous · Accepted solution]

Figured it out!

To calculate the running total while preserving filters from other tables and excluding the date table, I had to modify the FILTER function.

NetQuantityRunningTotal =
CALCULATE(
SUM('transactionrecord'[net_quantity]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)

By calling ALL on the Date Table, I was able to remove any filtering on the date table.

Running total was preserved across the entire time span and now when I filter by the date, the initial value does not reset to '0'.

Thanks for the assistance!