Running total with use of Row data

[kalspiros]

Hi all,

An easy query, i guess... googled a lot, searched a lot, can't seem to find a way to do that:

each [invoice_id] has a certain value as seen at [Gross amount invoiced].

separate payments are recorded for each [invoice_id], most of the times just one, in other occasions, more than one payment.

i am looking for a measure to provide the running sum of [Gross amount paid].

like this:

in case it's of any interest, here are the existing measures:

Many thanks in advance!

[milanpasschier2]

Hey, can you share some sample data (xlsx/pbix) so I can have a look? Best, Milan

[kalspiros]

Hey @milanpasschier2 , hmm... that will take some time because my spreasheet is linked with external .csv's and must remove all sensitive information prior sending... will try to send tomorrow

Cheers

[milanpasschier2]

Hey, thanks! Just a few rows is fine!

[kalspiros]

So @milanpasschier2 and all, here is the file i'm trying to work out.

Account Receivable aging_PBI.xlsx

(sorry just me being stupid, guess we can't upload a file on the message? couldn't find reference in the community)

You can find a column with the desired outcome as explained above. just to note that current formulas work fine when fields are collapsed. it's when they are expanded that things go south. @amitchandak i have already included your measure within the file

Many thanks

[milanpasschier2]

Hey, I guess you want this measure to work in Excel. I can't find a solution for this too 🤔

[kalspiros]

As apposed to PBI? nope, i would like that in Excel. i can easily set a new field on Power Query but was hoping for a measure 🙂 

[kalspiros]

Many thanks @amitchandak but that doesn't quite work out:

got the calendar ready in power query:

let
    Source = {Number.From(Start_Date)..Number.From(End_Date)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}, "en-US"),

named "Calendar" with [Date] as the actual date.

all connected in power pivot:

got the measure in place:

=CALCULATE(SUM(Analysis[payment_amount_gross]),FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])))

and this is the result when collapsed:

and that's when expanded:

working out through all dates of the calendar table 😕

Cheers

[amitchandak]

@kalspiros , Create a date table join it with your payment date and then this type of measure will work

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=max(date[Date])))