Cumulative sums in PowerPivot

Question

My data model looks like this:

• table Invoice with fields ID, Date, Amount and Customer
• table Payment with fields InvoiceID, Date and Amount

I'd like to plot a graph displaying the percentage of payments compared to the payment requests over time (e.g. by week).

I also want to have data slices filtering by customer or by Invoice date.

How would I do this in PowerPivot? I have a calculated column for the Payment table called WeeksAfterInvoice, which allows me to group the payments accordingly.

I also have these calculated fields:

TotalInvoiceAmount:=SUM(Invoice[Amount])

and

CumulativePayments:=SUMX(FILTER(ALLSELECTED(Payment);Payment[WeeksAfterInvoice]<= MAX(Payment[WeeksAfterInvoice]));Payment[Amount])

and

CumulativePaymentsPerc:=Payment[CumulativePayments]/Invoice[TotalInvoiceAmount]

which kind of does, what I want.

But when I filter by customer, I get strange results. If WeeksAfterInvoice has e.g. a range from 0 to 6 and I filter by a customer, who paid everything within 3 weeks, the values for 4-6 will be the same as the one for 0.

How can I avoid that?

Answer 1

Check this out. The Cumulative Total pattern allows you to perform calculations such as running totals, and you can use it to implement warehouse stock and balance sheet calculations using the original transactions instead of using snapshots of data over time.