Forum Discussion

Kristian_nho's avatar
Kristian_nho
Icon for Helper I rankHelper I
5 years ago

Cumulative sum not working

Hello all,

 

I have been through various answers on this page but non of them could help me so far.

The reqest is to show a dynamic running cumulative total that could be sliced with date slicers (year, month etc.).

 

The following formula works but it calculates the cumulative total from the first day in the table and does not take into account the date slicer:

 

 

GSV RT = 
VAR MinDate = MIN('Accounting Date'[Accounting Date])
VAR MaxDate = MAX('Accounting Date'[Accounting Date])

RETURN
CALCULATE(
    [GSV],
    FILTER(
        ALLSELECTED('Accounting Date'[Accounting Date]),
        'Accounting Date'[Accounting Date] <= MaxDate
    )
)

 

 

- when the year slicer is applied, the first day contains the cumulative total of the past years (30,000 in the table below) as well so it doesn't start from 0

 

DateGSVGSV RT CurrentGSV RT Correct
1/1/2021130,0011
2/1/202110030,101101
3/1/202110030,201201
4/1/202120030,401401

 

 

When I use the formula below, the cumulative sum stops working alltogether and the data is shown as if I used the SUM(Sales table [GSC]) instead:

 

 

GSV RT = 
VAR MinDate = MIN('Accounting Date'[Accounting Date])
VAR MaxDate = MAX('Accounting Date'[Accounting Date])

RETURN
CALCULATE(
    [GSV],
    FILTER(
        ALLSELECTED('Accounting Date'[Accounting Date]),
        'Accounting Date'[Accounting Date] <= MaxDate &&
        'Accounting Date'[Accounting Date] >= MinDate
    )
)

 

 

 

DateGSVGSV RT CurrentGSV RT Correct
1/1/2021111
2/1/2021100100101
3/1/2021100100201
4/1/2021200200401

 

I have tried using 'ALL' instead of 'ALLSELECTED' as well but with no change whatsoever.

 

Any advice is greatly appreciated.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Of course ALL and ALLSELECTED in this case return the same results. If you want to know why... you'll have to read this: The definitive guide to ALLSELECTED - SQLBI

     

    Also, it really does matter which columns you use in your formulas, which columns have filters placed on them and what the relationships are. It really does matter.