cancel
Showing results for
Did you mean:
Highlighted
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.

```Customers - # with 2nd Orders =
CALCULATE(
COUNTROWS(
FILTER(
CALCULATETABLE(
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
New Contributor

## Re: Refer to calculated table in measure.

```Customers - # with 2nd Orders =
COUNTROWS(
FILTER(
CALCULATETABLE(
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
)
)```
4 REPLIES 4
New Contributor

## Re: Refer to calculated table in measure.

```Customers - # with 2nd Orders =
COUNTROWS(
FILTER(
CALCULATETABLE(
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
)
)```
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.

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.

Member

## Re: Refer to calculated table in measure.

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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 220 members 2,433 guests
Recent signins: