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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kalspiros
Helper I
Helper I

Running total with use of Row data

Hi all,

 

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

kalspiros_0-1654626805490.png

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:

kalspiros_1-1654627017583.png

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

kalspiros_2-1654627101057.png

 

Many thanks in advance!

8 REPLIES 8

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

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

Hey, thanks! Just a few rows is fine!

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

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

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
Helper I
Helper I

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:

kalspiros_0-1654672955662.png

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:

kalspiros_1-1654673059770.png

and that's when expanded:

kalspiros_2-1654673094417.png

working out through all dates of the calendar table 😕

 

Cheers

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors