topic Re: Sum of calculation up til x month including previous months in DAX Commands and Tips

Sum of calculation up til x month including previous months

sdelpo — Mon, 21 Aug 2023 14:58:13 GMT

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)])

)

Re: Sum of calculation up til x month including previous months

Anonymous — Wed, 23 Aug 2023 02:16:37 GMT

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.

Re: Sum of calculation up til x month including previous months

sdelpo — Wed, 23 Aug 2023 13:49:28 GMT

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$

Re: Sum of calculation up til x month including previous months

Anonymous — Thu, 24 Aug 2023 08:08:06 GMT

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