Using VARS with CALCULATETABLE

[Roseventura]

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.

[Roseventura]

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

[v-eqin-msft]

Hi @Roseventura ,

Any updates?

You could refer to the blogs below to know How to provide sample data in the Power BI Forum and How to Get Your Question Answered Quickly

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Writing measures is the correct approach.  Share some data and show the expected result.

[AlexisOlson]

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.

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.

[VahidDM]

HI @Roseventura 

Read this : 

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/