Can you use VARs with CALCULATETABLE (or SUMMARIZECOLUMN) ?

[Roseventura]

...Just a general question I guess...

Can you use VARs with CALCULATETABLE?

Here's my issue:

I'm trying create a virtual or intermediate table which shows these results:

All Customers where the first date they ordered is within user-selected Churn Time Period (can be from 30 to 390 days)

AND their totals sales from the first order date thru the current date and the count of those orders.

Assume the user selected 60 days from the filter and the current date is 10/13/21, then they want to see

all orders for customers who ordered for the very first time since 8/13/21, the total Sales $$ for

all their orders, and how many orders they placed.

The resulting table would show this:

Customer	First Order Date	Total $$ Since First Order	No. Orders
NEAC	8/17/2021	264	1
AZAX	8/18/2021	3,333	4
UKBQ	8/23/2021	5,660	5
WAB4	8/24/2021	1,020	2
ITKR	8/31/2021	958	5
MAA7	9/1/2021	4,210	7
DECZ	9/2/2021	178	2
CAH6	9/3/2021	2,020	4
CNBH	9/3/2021	338	3
MNDK	9/8/2021	7,725	9
ITDC	9/10/2021	45	1
CAA5	9/13/2021	79	1
ITLK	9/13/2021	825	5
GYKV	9/21/2021	633	6
ITK9	9/21/2021	5,152	12
IHCB	9/22/2021	1,996	9
TXD7	9/22/2021	2,892	14
AKAW	9/27/2021	759	3
CAH7	9/29/2021	856	3
ITGY	9/29/2021	1,978	8
ZDHH	10/1/2021	250	5
INC7	10/4/2021	18,206	24
ITGO	10/4/2021	262	4
UTCZ	10/6/2021	459	4

I tried using CALCULATETABLE, SUMMARIZE, SUMMARIZECOLUMN and neither will let me specify variables (or I'm doing something wrong).  The VAR needs to be the Churn Time Period Value (user selected).  Every video I've watched shows how to use these measures, but they all hard-code in the variable  (i.e., [Date] = 10/13/2020 ).  What if the date isn't known?  The user needs to decide the time period.

[V-pazhen-msft · Accepted solution]

@Roseventura 
You cannot create a table using measure, neither CALCULATETABLE or SUMMARIZE. What you can do is create a var table and refer to the table content in the return. For example.

Measure =

VAR SelectedTime = 'Churn Time Period'[Churn Time Period Value]
var virtual table_ =SUMMARIZE(Shipments, Customers[CUSTOMER],Shipments[Date], "Total Sales", MAX([Total Sales]), "Total Customers", MAX([Total Customers]))

Return

Sumx(Filter(virtual table_ ,[Date] < today() - SelectedTime), [Total Sales])

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly

[Roseventura]

Ibendlin,

I use VARs a lot in my measures, but I've never used them in a table measure which is what I'm trying to do here.  And it's not working.

The above table that I provided IS the desired result.  I have a data set that goes back to 2019 thru current, so if the user chose 60 from the Churn Time Period filter, it would look ONLY at those customers who had their first order ever with us sometime between 8/13/21 and 10/13/21 (60 days).  Here's the table measure I'm using:

Calculatetable =

VAR SelectedTime = 'Churn Time Period'[Churn Time Period Value]

return

CALCULATETABLE(SUMMARIZE(Shipments,

Customers[CUSTOMER],

Shipments[Date]),

filter( Shipments, Shipments[Date] < today() - SelectedTime ),

"Total Sales", [Total Sales],

"Total Customers", [Total Customers] )

The above table measure results this message, but I'm not sure where in the table measure this is occurring:

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[lbendlin]

Calculatetable =
VAR TodayMinusSelectedTime = today() - 'Churn Time Period'[Churn Time Period Value]
return
CALCULATETABLE(SUMMARIZE(Shipments,
Customers[CUSTOMER],
Shipments[Date]),
filter( Shipments, Shipments[Date] < TodayMinusSelectedTime ),
"Total Sales", [Total Sales],
"Total Customers", [Total Customers] )

[Roseventura]

Hi Ibendlin,

I'm still getting the "True/False expression...." error message with your suggestion.  I'm not sure where in the measure that True/False error is occurring.  I removed the filter portion of the measure thinking maybe that was causing the error, but I still get the error.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[lbendlin]

Not only can you use variables, you can even use them inside each of the parameters of any of the DAX functions and you can nest them as often as you want.  Remember that you need to RETURN a value in that case.

You already provided sample data - can you also indicate the expected outcome?