Power BI - Running Totals with Filters and Slicers

Question

I want to create running totals (using a measure). However, if I apply slicers I want the values to adjust according to this selection.

For example I have a table with my clothes purchases for 2017-2018 (date of purchase;brand of product; product type; cost amount).

If I slice for Hugo Boss brand; year 2018 I want my March value to retrieve the sum of costs of all Hugo Boss products between first day of the raw table (in this case 1st of January 2017) and the last day of the month in my selection (in this case: 31st of March 2018).

At the moment my function is as shown below but does not work.

Cumulative =
CALCULATE (
    SUM ( 'Clothes Purchases'[Cost] ),
    FILTER (
        ALL ( 'Clothes Purchases' ),
        'Clothes Purchases'[Acctg Date] <= MAX ( 'Clothes Purchases'[Acctg Date] )
    )
)

Can someone please help?

Thanks, Domingos

Solution 1

First, create a calculated column in the table for the Year-to-Date total, you will reference this later in your measure:

Cumulative Cost = TOTALYTD(SUM('Clothes Purchases'[Cost Amount],'Clothes Purchases'[Date])

To filter down use the ALLEXCEPT clause in your measure and specify the filter columns:

RunningTotalMeasure = CALCULATE( SUM( Clothes Purchases'[Cumulative Cost] ),
    FILTER( ALLEXCEPT( 'Clothes Purchases'[Brand of Product], 'Clothes Purchases'[Product Type]), 
    'Clothes Purchases'[Date] <= MAX( 'Clothes Purchases'[Date] ) ) )

I recently experienced this issue as well and have had sleepless nights over it (props to the gems scattered here and there). I hope it is of help.

Solution 2

I created a simple table according to the values you require:

Date of Purchase    Brand of Product    Product Type    Cost Amount
03/03/2018          Hugo Boss           Watch           $300
01/05/2017          Kate Spade          Jeans           $130
28/07/2017          Givenchy            Perfume         $212
19/10/2017          Adidas              Activewear      $179

And then created a measure on the same table in Report mode, using:

Running Total = CALCULATE(SUM('Clothes Purchases'[Cost Amount]),
                          FILTER(ALLSELECTED('Clothes Purchases'[Brand of Product]),
                          ISONORAFTER('Clothes Purchases'[Brand of Product],
                          MAX('Clothes Purchases'[Brand of Product]), DESC)))

Then using a slicer on Brand of Product and Date of Purchase, here is the running total without any values chosen in the slicers (test by adding all of the costs together):

And here is the running total with just "Hugo Boss" chosen on the slicer according to the dates you require:

I hope this helps! :)

Comments

[Domingos Oliveira]

Dmrmyrnz, thanks for your reply but unfortunately it is not working. I most likely did not make myself clear. It is first time I am writing on stackoverflow and I am new to power bi. In the second example you are only showing the 1 value for Hugo Boss.. 300$. What I would like to see is if we had more rows of Hugo Boss products for different dates such as 28/02/2017; 01/03/2018; 13/05/2018 and we select month of May for 2018 I want the number to add values since the beginning of time until the latest date of 2018 May for the displayed brand. Also I don't want it to be limited to brand only...

[Admin]

@DomingosOliveira it should work even if you have multiple purchases of Hugo Boss. I only put one purchase in my example but it should filter to the Hugo Boss brand.

[Admin]

You can place filters on anything except for Measures, so you don't have to be limited to just the brand -- the Date of Purchase is also a filter. Perhaps if you show an update I can help you more with your problem.

[Domingos Oliveira]

hI, i really appreciate your help but it's simply not working for the reasons i already stated. also the measure is filtering brand of product specifically, i need it to allow all possible column filtering so that it would work for product type as well...

[Admin]

No problems, thanks for your response. When I get some free time today I will show you how to create measures using the exactly same method as above and show that it works, not just for the small example above. To create more filters, you will actually need several different filters: one for brand product, one for product type, one for date, and potentially one for purchase value. I'll create a new response later on (possibly) today.