Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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's avatar
    Anonymous
    7 years ago

    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!

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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' ?