Sum of calculation up til x month including previous months

[sdelpo]

I am trying to do a rolling total of data. I got to this dax command but that only give me the total of the current balance from today. I want to be able to see the total ending balance of each month to compare this month's total to previous months. 
Is there an easy way to do that?

Balance = VAR Maxdate = MAX(ExtractReport[InvoiceServiceDate (bins)]) 

Return

CALCULATE(

SUM(ExtractReport[Charge])

+ SUM(ExtractReport[Tax])

- SUM(ExtractReport[WA])

- SUM(ExtractReport[AA])

- SUM(ExtractReport[Adjust])

- SUM(ExtractReport[WriteOff])

- SUM(ExtractReport[Refund])

- SUM(ExtractReport[SCRefund])

- SUM(ExtractReport[SCPayment])

- SUM(ExtractReport[AppliedPayment])

- SUM(ExtractReport[Payment]),

ExtractReport[InvoiceServiceDate (bins)] <= Maxdate,

all(ExtractReport[InvoiceServiceDate (bins)])

)

[sdelpo]

Not just within the current month, but I'm looking to have the total of all balances that carryover at the end of each month.

Ex:

month 1: +300$ -200$

Month 2: +400$ -100$

Month 3: +100$-130$

Month 4: +230$ -100$

Month 5: +300$ - 250$

Month 6: +100$ - 200$

I want the results to be

Month 1: 100$

Month 2: 400$
Month 3: 370$

Month 4: 500$

Month 5: 550$

Month 6: 450$

[Anonymous]

Hi @sdelpo 

Try the following code

Balance = VAR Maxdate = MAX(ExtractReport[InvoiceServiceDate (bins)])

 

Return

 

CALCULATE(

 

SUM(ExtractReport[Charge])

 

+ SUM(ExtractReport[Tax])

 

- SUM(ExtractReport[WA])

 

- SUM(ExtractReport[AA])

 

- SUM(ExtractReport[Adjust])

 

- SUM(ExtractReport[WriteOff])

 

- SUM(ExtractReport[Refund])

 

- SUM(ExtractReport[SCRefund])

 

- SUM(ExtractReport[SCPayment])

 

- SUM(ExtractReport[AppliedPayment])

 

- SUM(ExtractReport[Payment]),

FILTER(ALL(ExtractReport),ExtractReport[InvoiceServiceDate (bins)] <= Maxdate))

 

And can you provide some sample data, based on the information you have offered, the dax your wrote is right. the picture you offered cannot see the problem.

Best Regards!

Yolo Zhu

[Anonymous]

Hi @sdelpo 

Do you want to calculate the total of each month?

If you want to achieve this goal, you can modify the measure to the following

Balance =
VAR Maxdate =
    MAX ( ExtractReport[InvoiceServiceDate (bins)] )
RETURN
    CALCULATE (
        SUM ( ExtractReport[Charge] ) + SUM ( ExtractReport[Tax] )
            - SUM ( ExtractReport[WA] )
            - SUM ( ExtractReport[AA] )
            - SUM ( ExtractReport[Adjust] )
            - SUM ( ExtractReport[WriteOff] )
            - SUM ( ExtractReport[Refund] )
            - SUM ( ExtractReport[SCRefund] )
            - SUM ( ExtractReport[SCPayment] )
            - SUM ( ExtractReport[AppliedPayment] )
            - SUM ( ExtractReport[Payment] )
    )

If this cannot meet your requirement, can you provide some sample data and the output you want?

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.