cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maracles Member
Member

Refer to calculated table in measure.

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

- Their first ever order must be within 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. 

snip_20160215171432.png



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()
						
					)							
			)
		)
	)
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
greggyb New Contributor
New Contributor

Re: Refer to calculated table in measure.

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
	)
)

View solution in original post

4 REPLIES 4
Highlighted
greggyb New Contributor
New Contributor

Re: Refer to calculated table in measure.

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
	)
)

View solution in original post

maracles Member
Member

Re: Refer to calculated table in measure.

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 New Contributor
New Contributor

Re: Refer to calculated table in measure.

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 Member
Member

Re: Refer to calculated table in measure.

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 323 members 3,244 guests
Please welcome our newest community members: