Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Sum of calculation up til x month including previous months

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

3 REPLIES 3
Frequent Visitor

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\$

Community Support

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

Community Support

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.

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors