Forum Discussion

voidbydefault's avatar
voidbydefault
Helper I
3 years ago

Problem with cumulative(running) total

Hi,

I am trying to calculate a cumulative/running total using:

 

Running Invoiced Amounts = 
VAR CurrentDate = MAX(AR_Balances[GL Date])
RETURN

CALCULATE(
    SUMX(AR_Balances, AR_Balances[Invoice Amount]),
    FILTER(
        ALL(AR_Balances),
        AR_Balances[GL Date] <= CurrentDate
    )
)

 

 

Above gives matrix following output:
GL Date*Invoice AmountCumulative Total
1-Jan-221001,050
15-Jan-222001,250
.........
31-Dec-222001,450
1-Jan-232501,700
.........
27-May-233002,200
Total1,0503,050

*GL Date = Invoice Date

 

Problems I am unable to fix::


1. Starting of cumulative total (1,050) is the sum of all invoices (total). Why and how to prevent so?

2. Cumulative total seemingly works (if starting 1,050 is ignored), but I expect it to reset as soon as the year changes on 31-Dec. How to achieve this?

 

Thanks.

3 Replies

  • voidbydefault's avatar
    voidbydefault
    Helper I
    3 years ago

    Update:
    I have managed to fix the first issue where cumulative total started with the sum of all invoices (i.e. 1,050) using below DAX. Please help me with the second problem. Thanks:

     

    Solution to # 1:

    Running Invoiced Amounts = 
VAR CurrentDate = MAX(AR_Balances[GL Date])
RETURN

CALCULATE(
    SUMX(AR_Balances, AR_Balances[Invoice Amount]),
    AR_Balances[GL Date] <= CurrentDate
)