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.

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

sdelpo_1-1692629805709.png

 

3 REPLIES 3
sdelpo
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$

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

v-xinruzhu-msft
Community Support
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors