Forum Discussion
Cash Velocity Cohort Analysis
I am trying to create a cash velocity waterfall analysis utilizing two date ranges, date of service and date of payment to analyze how quickly payments are made based on the relative date of service charge and show the payment as a percentage of charge. All payments are related to a specific date of service; a charge can have multiple dates of payment.
For example, on date of service for January 2020, $100 was billed as a gross charge, and collected the following payments related to the January 2020 date of service (cohort): in January 2020 collected $20 , in February 2020 collected $30, in March 2020 collected $20. This is just an example, but I have provided data below to articulate the desired PowerBi solution
The 5% in Desired Solution #1 for January 2020 is calculated as 3,299 divided by 67,945; the 16% is calculated as 10,841 divided by 67,945.
The 2% in Desired Solution #1 for February 2020 is calculated as 1,571 divided by 63,459; the 17% is calcuated as 10,664 divided by 63,459.
Desired Solution #2 is the summation of Desired Solution #1 to calculate a cumulative %.
Thanks in advance.
2 Replies
- AnonymousNot applicable
Hi Anonymous ,
Could you please provide desensitized example data? It is very helpful for me to test. I am a little confused about your needs for Desired Solution #2, Could you please explain them further? It would be nice if the calculation logic was given.
Thanks for your efforts & time in advance.
Best regards,
Community Support Team_Binbin Yu- AnonymousNot applicable
Hi Anonymous ,
Appreciate the help. I am having trouble uploading a sample datafile on the community board - any suggestions how to do that?
For the Desired Soltuion #2, this is a cumulative payments as a % of charge by date of service. The logic would be to sum all of the payments by date of service as a % of the relative charge date of service. I was able to post a picture of the calculation in Excel showing the formula. Hopefully this helps.
Appreciate the help.