Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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 🙂
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
@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])))
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |