Power Pivot Running totals

[shophoney]

Hello,

I'm trying to figure out how to build a calculated column with a running total of payments against an invoice.

So below is an example:
Invoice #3322999 is for $14,387.86. We have 2 credits against the invoice. CN3325633 $167.44 and CN 3326818 $229.32

I'd like to see the Original amount less 1st credit = 14220.42
2nd credit = $13991.10

I need to provide a list of payments or credit and the balance.

This can be either with a measure or calculated column. Not sure which is better.

I tried pulling in the TrasactionAmount and selecting running total. But that didn't work.

Thanks

 

[mart37]

=
VAR ref=Table1[RefrenceNumber]
VAR Amount=Table1[Amount]
RETURN CALCULATE(Amount+
    SUM(Table1[TransactionAmount]);
    FILTER(Table1;Table1[RefrenceNumber]=ref &&Table1[TransactionAmount]>=EARLIER(Table1[TransactionAmount]))
    )

 

[shophoney]

=
VAR ref=Table1[RefrenceNumber]
VAR Amount=Table1[Amount]
RETURN CALCULATE(Amount+
    SUM(Table1[TransactionAmount]);
    FILTER(Table1;Table1[RefrenceNumber]=ref &&Table1[TransactionAmount]>=EARLIER(Table1[TransactionAmount]))
    )

Hi,

Just to confirm this should be added to power Query?

I tried both making a measure and calculated column. But couldn't.

It's so easy to make a formula within Excel to address this. But given the range, vendor list and invoice list changes. It just wont work.

Best to have everything in the back end, Power Query or Power Pivot.

Thanks for your help.

 

[mart37]

Sorry this is for a calculated column in Power Pivot.

 

[shophoney]

Sorry this is for a calculated column in Power Pivot.

Thanks,

I put it in as a Calculated Column.

But the results seem wrong. I've displayed what the totals should be beside it.

=

VAR ref='Bill HISTORY'[ReferenceNumber]

VAR Amount='Bill HISTORY'[Amount]

RETURN CALCULATE(Amount+

SUM('Bill HISTORY'[TransactionAmount]), FILTER('Bill HISTORY','Bill HISTORY'[ReferenceNumber]=ref &&'Bill HISTORY'[TransactionAmount]>=EARLIER('Bill HISTORY'[TransactionAmount]))

)

 

[I_cant_read]

I think it is easier if the data is within the same column to do a running total.

+14387.86
-167.44
-229.32

You could accomplish it using List.Sum and List.Range in the M code.

 

[mart37]

See that you have added a date column. Try this:

 

[shophoney]

See that you have added a date column. Try this:
View attachment 34864

Wow, i think that did it. I was looking at this for way to long. Just couldn't see the forest through the trees.

Thanks for your help.