cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 220 members 2,433 guests
Please welcome our newest community members: