Using VARS with CALCULATETABLE

Question

Could someone please explain to me why this works:

LastPurchaseDates =

VAR Churnvalue = 365

RETURN

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE(

Shipments,

Customers[CUST_CODE],

"Total Sales", [Total Sales]

),

"LastPurchaseDate", CALCULATE(MAX( Shipments[Ship Date] ))),

Shipments[Ship Date] < today()-churnvalue

)

But this does NOT:

LastPurchaseDates =

VAR Churnvalue = SELECTEDVALUE('Churn Time Period'[Churn Time Period])

RETURN

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE(

Shipments,

Customers[CUST_CODE],

"Total Sales", [Total Sales]

),

"LastPurchaseDate", CALCULATE(MAX( Shipments[Ship Date] ))),

Shipments[Ship Date] < today()-churnvalue

)

I need to reference the Chur Time Period Value as that can be changed by a user. I can't get this to work

unless I hard-code '365' into the measure.

I basically need a table showing the Customer Code, the last purchase date (Ship date), and the total Sales, but I only want the table to return Customers who have not purchased anything in over "N" time period (Churn Time Period Value), selected by user.

How can I get this to work?

Thanks.

alexisolson · Answer

A calculated table cannot be dynamic based on user selection since calculated tables (and calculated columns) are only calculated once each time the data model is loaded or refreshed rather than in response to slicers or filters from the report page.
&nbsp;
On the other hand, this is what measures are designed to do. So your ultimate goal is very likely possible--you just need a slightly different approach.

ashish_mathur · Answer

Hi,
Writing measures is the correct approach.&nbsp; Share some data and show the expected result.

vahiddm · Answer

HI&nbsp;Roseventura&nbsp;&nbsp;Read this :&nbsp;https://radacad.com/caution-when-using-variables-in-dax-and-power-bi&nbsp;If this post&nbsp;helps, please consider&nbsp;accepting&nbsp;it as the solution&nbsp;to help the other members find it more quickly.Appreciate your Kudos!!LinkedIn:&nbsp;www.linkedin.com/in/vahid-dm/&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Roseventura&nbsp;,
&nbsp;
Any updates?
&nbsp;
You could refer to the blogs below to know&nbsp;How to provide sample data in the Power BI Forum&nbsp;and&nbsp;How to Get Your Question Answered Quickly
&nbsp;
&nbsp;
Best Regards,Eyelyn QinIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

roseventura · Answer

I need to submit sample data.&nbsp; Please do not close this issue.

Forum Discussion

Using VARS with CALCULATETABLE

5 Replies

Related content

Can you use VARs with CALCULATETABLE (or SUMMARIZECOLUMN) ?

Direct Query and using CalculateTable

NETWORKDAYS using Date VAR

VAR and SWITCH Output issues

FILTER vs CALCULATETABLE