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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors