Help with Cohort Analysis on Expiration Amount

[lzhang17]

Hello,

I was wondering if anyone could help with creating a cohort analysis/matrix for the following senario: 

The goal here is to see if there is anything left in the revenue that isn't spent before it's expiration date. The screenshot below is one way displaying the information built in Excel. Revenue could come in any month, and each revenue would expire in 24 months. We are trying to group them into the Month 00 column with the month in rows (y axis).The expense would also occur in any month but we are trying to assign the amount on a first come first serve basis where it will follow it's original occured date along the x-axis (columns) but also follow the right grouping of row. In our example, the expense would reach 24,000 beforer going to the next month of revenue for expense. 

Please let me know if this is achievable through dax or if there is any better way to structure and present this information. We want to see if there is value left unspent by the time it expires and another way to look at it would be by the end of a fiscal year end, is there any amount left and if so, which month is driving the number?

I've also created a similar mock up dashboard to showcase the data structure that we have.

Really appreciate any inputs. Thank you!

https://drive.google.com/file/d/1CjWDU-1DNZUIihjriEgmgPNQeI3-5PHX/view?usp=sharing 

[amitchandak]

@lzhang17 , Need to check the files.

But refer to my blog and video if that can help

https://youtu.be/Q1vPWmfI25o?t=754

#PowerBI Customer Retention Part 3: Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...