Cohort Analysis Revenue

[Anonymous]

Hi All - I'm stuck on developing a cohort analysis.  I'm looking for a measure that will calculate the Revenue generated 0-12 months after the cohort date according to my column values.  I've watched/read a few tutorials, but still can't figure out the DAX to get this measure working properly.  

My cohort is based on the month of a Partner's creation date.  With a creation date of 2/8/2018, the cohort would be Feb 2018.  Month 0 would be any revenue generated for this cohort in Feb 2018, Month 1 would be rev generated for this cohort in Mar 2018, Month 2 for Apr 2018, etc.

I've attached my sample PBIX.  Any input on the measure to accomplish below with my data model would be greatly appreciated!

PBIX

https://securisync.intermedia.net/im/s/SODdvhwN8Cu3X8y7154Wc1000fc297

Excel Dataset

https://securisync.intermedia.net/im/s/cK2cC5harJg1qHhpyuHcYD000fc297

Below is an example of my desired result. 

[Anonymous · Accepted solution]

// original
Cohort Revenue =
VAR CohortStartMonth =
    SELECTEDVALUE( 'Transactions'[Cohort] )
VAR CurrentMonth =
    EOMONTH(
        CohortStartMonth,
        SELECTEDVALUE( 'Periods Out'[Value] )
    )
VAR TransactionsInCohortPeriod =
	// You should never attempt a filter over
	// a fact table involving RELATED. This kills
	// performance because RELATED uses
	// CALCULATE behind the scenes and this
	// in turn effects context transition which
	// makes your system die... as you've witnessed.
    FILTER(
        'Transactions',
        RELATED( 'Date'[EndOfMonth] ) = CurrentMonth
    )
VAR Invoices =
	// SUMMARIZE should never be used to do
	// any calculations in it. NEVER. There are myriads
	// of reasons but I don't have time to explain.
	// SUMMARIZE is safe only if you do grouping and
	// nothing else. On top of that, it looks like this
	// summarization is completely redundant since
	// later on you do a SUMX over the total column
	// anyway. Partitioning the table by PartnerID
	// is also totally useless in this case because of
	// the final summation.
    SUMMARIZE(
        TransactionsInCohortPeriod,
        'Transactions'[PartnerID],
        "Invoice Total",
            SUMX(
                'Transactions',
                Transactions[amount]
            )
    )
RETURN
    SUMX(
        Invoices,
        [Invoice Total]
    )


// Here's probably what you want:

[Cohort Revenue] =
VAR CurrentMonth =
    EOMONTH(
        SELECTEDVALUE( 'Transactions'[Cohort] ),
        SELECTEDVALUE( 'Periods Out'[Value] )
    )
VAR InvoicesTotal =
	calculate(
		sum( Transactions[amount] ),
		keepfilters( 
			'Date'[EndOfMonth] = CurrentMonth 
		)
	)
return
	InvoicesTotal

[Anonymous]

Thanks, daxer!! This is a much simpler measure for me to understand and accomplishes the exact same result in my sample data set.  Unfortunately, I still get the memory error.  

 I have to assume this is a problem with the calculated columns I added to my model at this point (2 in DIM table, 2 in fact table).  I will try to set the model up differently and see if I can get this to work with my real dataset.

Also thanks for tips on the use of RELATED and SUMMARIZE...will keep that in mind moving forward.

[Anonymous]

@Anonymous 

2 things to address. First, if my code does what you need, please mark the answer as the answer. Kudos would be appreciated as well if you don't mind. Second, you should never create calculated columns in your model via DAX. There are many reasons behind it, several chapters in a book could be written. Always use your source or Power Query to calculate columns and then load them into the model. ALWAYS, if you want to have a peace of mind... that is.

[Anonymous]

I ended up solving the memory issue by removing the 2 calculated columns from my fact table and adding a calculated "Cohort Date" table to filter my partners table.  Just needed to swap out one of the SELECTEDVALUE columns in your measure.  The command works quickly now.  Very much appreciate your help on this, have a great day!!!!

[Anonymous]

Glad you've pulled this off. If you have a good healthy star-schema model, you can count yourself among those who are lucky enough to understand that it's the only way to do it RIGHT and have simple, fast DAX. Give yourself a pat on the back.

[Anonymous]

I've seen so many cohort analyses on the Power BI Community Forums (and there will also probably be many in the PBI Gallery) that it would be a matter of minutes to find something that suits your needs. Please use the Search box...

[Anonymous]

I figured out a measure to work with my sample dataset.  However, when I run the query with my actual dataset, I receive an error that the calculation is too memory intensive.  Do you have any suggestions on how to optimize the DAX here for a large dataset?

This is my sample model.

This is my measure: it works with my sample dataset, not my actual dataset.

Cohort Revenue =

VAR CohortStartMonth =

SELECTEDVALUE ( 'Transactions'[Cohort] )

VAR CurrentMonth =

EOMONTH(CohortStartMonth, SELECTEDVALUE ( 'Periods Out'[Value] ) )

VAR TransactionsInCohortPeriod =

FILTER(

'Transactions',

RELATED('Date'[EndOfMonth]) = CurrentMonth

)

VAR Invoices =

SUMMARIZE(

TransactionsInCohortPeriod,

'Transactions'[PartnerID],

"Invoice Total", SUMX('Transactions', Transactions[amount])

)

RETURN

    SUMX(Invoices, [Invoice Total])

This is the error I get with my actual datset.