Creating cumulative (running) total which responds to date slicer

[Anonymous]

Hi  All, 

I have been trying to figure out the cumulative total for quite some time, but I have had no luck so far.  The sample data can be located in the following link:

Sample File

So in the Cumulative value sheet, I have added years with the sum of the all locations in that year.  I can create a quick measure with the running total with the YTD filed.  However, when you apply a slicer it fails to show the correct cumulative total.  For example, in 2007 the cumulative value 4.0 million, but when you apply the slicer it would show a different incorrect value.  The idea is that even when the slicer is applied it shows the correct cumulative value.

Is there a way to do this?

EDIT: @TomMartens solution does work below.  However, @TomMartens suggested another solution to have a multiple filtering mechanism to calculate the same cumulative value. 

CALCULATE(
    SUM('Table 1[Field]')
    ,FILTER(
        ALLEXCEPT('Table 1', 'Table 1[Field]')
        ,'Table 1[Field]' <= MAX('Table1[Field]')
    )
)

Thanks @TomMartens for the solution; excellent, it worked perfectly.

(PS. edited the orignial table and field names to ensure confidentiality.)

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur, 

This does not meet the requirement fully, because if you add a slicer the cumulative value changes.  For example, if you add a slicer the orignial value is 7.1 million (from 1981 to 2017).  Then if you bring the starting dates earlier then the cumulative value is not 7.1 but something lower.  The idea is that even though the dates may change the total cumulative value should not change and should reflect the correct total value. 

[TomMartens · Accepted solution]

Hey,

not sure if I understand your requirement correctly, but using this DAX statement

calc Cumulative Value = 
CALCULATE(
    SUM(Table1[Total Value])
    ,FILTER(
        ALL('Table1')
        ,'Table1'[Year] <= MAX('Table1'[Year])
    )
)

allows to create this report

Hopefully this is what you are looking for

Regards

Tom

[rylach]

Hi Tom,

What if I would like to limit the dates with the slicer also from bottom? How to combine conditions

FILTER(ALL(table);table[date]<=MAX(table[date]))

and

FILTER(ALL(table);table[date]>MIN(table[date]))

?

[Anonymous]

@TomMartensThanks, this works.  But there is a slight problem, it double counts the values of the last year.  So in data set the total value is around 7.1 million (cumulative value of 2017). But, when we apply the cumulative formula it moves the 2017 value to 7.4.  Essentially, the formula is  adding the value for 2017 (300K) twice and reaching the 7.4 million figure.  

All the previous years values are correct working perfectly.  I believe the workaround is that it singles out the value of 2017 and deducts its from the cumulative total. 

[TomMartens]

Hey,

I can't reproduce your issue regarding the cumulative value for the Opening Date 2017. If I adjusted my proposed measure to your model

calc **bleep** GLA = 
CALCULATE(
    SUM('Base Data in power bi'[GLA])
    ,FILTER(
        ALL('Base Data in power bi')
        ,'Base Data in power bi'[Opening Date] <= MAX('Base Data in power bi'[Opening Date])
    )
)

I'm able to create this report

Hope this helps if not, please provide detailed information, how to recreate the issue, meaning naming selections in all slicers and stuff like this 🙂

Regards

Tom

[Anonymous]

@TomMartensjust a follow up question, is it possible to extract the maximum and minimum values from this calculated code? This will help me in calculating ratios.  I tried the min formula followed by the newly created calculation, but that did not work. 

[TomMartens]

Hey,

not sure if this will answer your question, but this is an example for retrieving the MAX value

calc max GLA = 
CALCULATE(
    MAX('Base Data in power bi'[GLA])
    ,FILTER(
        ALL('Base Data in power bi')
        ,'Base Data in power bi'[Opening Date] <= MAX('Base Data in power bi'[Opening Date])
    )
)

Please be aware, that due to your model, using ALL to adjust the Filter Context maybe limits the analytical power, you may consider to add additional tables to you model. One for the opening date and one for the locations.

Pease also consider to mark an answer as accepted solution, this will help others. It's allso a good practice to start a new question, this provides the possibility to others to join in and given an appropriate title, to help others 🙂

If you like an answer you also may give kudos, honoring the time that has been spent to compose the post and the insight it provides.

Regards

Tom

[Phil_Seamark]

Nice reply @TomMartens,