I need to build a time-based Cohort Analysis but I’m having trouble adapting the showcases and videos about this topic to my own needs.
Instead of a monthly cohort, I need a quarterly cohort. And instead of calculating when they make their second purchase, I need to see how many customers from that cohort made a transaction in each following quarter.
My main issue is that the DATEDIFF doesn’t let me use a measure as date and I can’t add a column in the sales table with a “Days since Join Date”, where a subtract the Order Date from the Join Date of that particular customer (because of circular references).
What does work for me, is a matrix with both rows and columns being Quarters in Year and Values is Unique Customers. But when I add the periods to the column part of the matrix it can’t find a relationship and I’m not sure how to fix that.
I haven’t found any information online that could help me. I’m not providing any of my dax calculations because I’m just adapting things from the videos and it’s not making much sense. I’m just looking for someone to point me in the right direction to be able to understand the logic behind making this work, Maybe I’m missing a key function or there’s already a video that talks about this?