Refer to calculated table in measure.

[maracles]

I am trying to identify the number of my customers who meet the following criteria:

- Their first ever order must be within x period
- Within x period they must also have placed at least 1 additional order. 

In this scenario the period is 90 days. 

I am identifying the customers order count by a column known as Customer - K2 Cumulative Orders. This is a calculated column that identify how many orders each customer has placed and assigns a count. Their first order is 1, their second 2 and so forth. This is not affected by filter context and is based on the full data table. 

I have the following measure to attempt this but it is  not working correctly. Instead of counting the number of customers over 90 days it is only calculating the number of customers within the period set by the table (in my case my table columns are month). 

For example in the table below, taking February, 7 is the number of customers that meet this criteria in February i.e. placed their first and a subsequent order in February. However I want it to show me the number of customers in the period from Today going back 90 days. 





I have the following measure, can someone please help...

Customers - # with 2nd Orders = 
	CALCULATE(
		COUNTROWS(
			FILTER(
				CALCULATETABLE(
					ADDCOLUMNS(
						VALUES('Orders Facts'[Customer - K2]),
						"Minimum Cumulative Order", CALCULATE(MIN('Orders Facts'[Customer - K2 Cumulative Orders])),
						"Maximum Cumulative Order",	CALCULATE(MAX('Orders Facts'[Customer - K2 Cumulative Orders])),
						"Minimmum Order Date", CALCULATE(MIN('Orders Facts'[Order Date]))
					)					
				),
				AND(
					AND([Minimum Cumulative Order] = 1,
					[Maximum Cumulative Order] > 1
					),
					AND([Minimmum Order Date] >= (TODAY()-90),
						[Minimmum Order Date] <=TODAY()
						
					)							
			)
		)
	)
)

[greggyb · Accepted solution]

Customers - # with 2nd Orders =
COUNTROWS(
	FILTER(
		CALCULATETABLE(
			ADDCOLUMNS(
				CALCULATETABLE(
					// Get all customers with first orders in last
					// 90 days
					VALUES( 'Order Facts'[Customer K2] )
					// Assuming the above is the customer key
					,'Order Facts'[Customer - K2 Cumulative Orders] = 1
				)
				// ADDCOLUMNS() arguments 2-N get the last
				// cumulative order number in the time frame
				,"MaxOrder"
				,CALCULATE( MAX( 'Orders Fact'[Customer - K2 Cumulative Orders] ) )
			)
			// Outer CALCULATETABLE() - date filter affects
			// everything defined above 
			,DATESBETWEEN(
				DimDate[Date]
				,TODAY() - 90
				,TODAY()
			)
		)
		// FILTER() argument 2
		,[MaxOrder] > 1
	)
)

[tmoran08765]

Hi Greggyb,

Can you help me understand what you mean by 2-N in your comment regarding the ADDCOLUMNS function?

I don't understand what you mean by 2-N.

Much appreciated

[tmoran08765]

I think you're talking about the args 2 through N, I get it.

Beautiful code and nice to know you can put in comments like that.

[maracles]

Thank you, that worked. You also made me realise that you can comment in the DAX - never knew that and very helpful!

One question, how does using TODAY() work in this. For example if I plot this information in a table using the same format as in my original post but extend it back to lets say April last 2015. What date does this calculation use as the starting point from which is works back 90 days, does it to it from the first of the month, last of the month, the current day number in the equivalent month?

Thanks.

[greggyb]

TODAY() returns the system date, it will return the same value in any filter or row context you could ever put it in. Based on your description and sample, that seemed to be the requirement. If this needs to be dynamic as of the date context, then you can change out the TODAY() function calls with MAX( DimDate[Date] ) or MIN( DimDate[Date] ) to have it be as of the last or first day of the month in context.

[maracles]

Ok, understood. Thanks for that, I've made the tweak now.