Forum Discussion
Running Total - Collections since billed
I am looking to create a series of charts that will calculate the following, relative to the month a customer was invoiced:
1) collections
2) cumulative collections
3) cumulative collections as a % of billings
My flat file has columns for month of payment and month of invoice, and I have been able to create the first chart with relative ease. However, I'm struggling with how to properly create measures for # 2 / # 3. Any suggestions?
Hi Anonymous ,
Please try the following measures:
Cunulative amount = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), FORMAT ( 'Table'[Date], "mmmmyy" ) = MAX ( Dates[MonthYear] ) && 'Table'[Order ID] <= MAX ( 'Table'[Order ID] ) ), [SumAmount] )Cunulative % = DIVIDE ( [Cunulative amount], SUM ( 'Billed Table'[Billed amount] ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Anonymous , I think this is like cohort , I think you need this additional measure than what I given in blog
Sales % = CALCULATE(divide(sum(Sales[sales]),CALCULATE(sum(Sales[sales]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =max('Customer Age Bucket'[Age]))) )
v-kkf-msftCommunity Support
Hi Anonymous ,
Please try the following measures:
Cunulative amount = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), FORMAT ( 'Table'[Date], "mmmmyy" ) = MAX ( Dates[MonthYear] ) && 'Table'[Order ID] <= MAX ( 'Table'[Order ID] ) ), [SumAmount] )Cunulative % = DIVIDE ( [Cunulative amount], SUM ( 'Billed Table'[Billed amount] ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
