Calculating new venue acquisitions/renewals based on a user assignable "last acquired date"

Stoned_Edge10 — Tue, 17 May 2022 12:17:48 GMT

Hi all,

I'm really enjoying being part of the Power BI community so far, so thanks to everyone who has made it a great experience. DAX I am finding powerful but really challenging to get my head around, especially creating virtual tables.

I am trying to count up the latest number of "new acquisitions" of venues based on an arbritarily assigned "days since last acquisition date for each customer" with a what-if parameter (i.e user can input this value). I've followed a few online tutorials/watched a few videos, and this is what I have come up with so far. However, this below DAX formula only counts up the unique client ID's that appear in "CustomerTM" but dont appear in "PriorCustomers" table. I need to sum a column of "total venues", rather than counting unique ID numbers (i.e clients).

New Customers within x Churn Frame =

VAR CustomerTM = VALUES (FACT_Actuals[ClientID])

VAR PriorCustomers =

CALCULATETABLE (

VALUES ( FACT_Actuals[ClientID] ),

FILTER (

ALL ( 'DIM_Date' ),

'DIM_Date'[Date]

> MIN ( 'DIM_Date'[Date] ) - [Churn Time Frame Value(s)]

&& DIM_Date[Date] < MIN ( DIM_Date[Date] )

)

RETURN

COUNTROWS ( EXCEPT ( CustomerTM, PriorCustomers ) )

My current table structure looks like this:

Client ID	LastDate (Per Client)	VenueNumber
1	09/01/2022	100
1	09/09/2022	200
1	10/01/2022	300
2	09/01/2022	400
2	10/01/2022	100
3	08/01/2022	200
3	09/01/2022	300
3	10/01/2022	400
4	10/01/2022	500
…		…
n		n

I would like to first create a virtual DAX table that has:

a) Unique client ID

b) Number of venues (showing only unique clients per row, based on the max date for each client in the above table)
c) Compare each unique clients maximum venue number (in the given filter context) to see if it existed -n days ago (i.e the [Churn TIme Frame Value(s)] what-if parameter below)

d) Sum the total venues that are new, based on each client (i.e if they appear n days ago, then consider them a "renewal", otherwise new

d) Show new customer acqusitions in a bar chart, with dates (months) on the x-axis and "new customer acquisitions" on the y-axis

e) Ideally, show renewals next to new customers as well, if they were found prior to >365 days

Clearly, the above doesn't do what I want to achieve. Does anyone have any solutions? It could either be a calculated column to assign each customer a "new or renewed status" but a measure would be ideal.

Hopefully I am clear enough on the required output... sorry if I was a bit unclear, happy to clarify any points.

Thanks so much for your help.

Benjamin

Re: Calculating new venue acquisitions/renewals based on a user assignable "last acquired date"

tamerj1 — Tue, 17 May 2022 13:12:09 GMT

Hi Stoned_Edge10

please try

New Customers within x Churn Frame = VAR CustomerTM = VALUES ( FACT_Actuals[ClientID] ) VAR PriorCustomers = CALCULATETABLE ( VALUES ( FACT_Actuals[ClientID] ), FILTER ( ALL ( 'DIM_Date' ), 'DIM_Date'[Date] > MIN ( 'DIM_Date'[Date] ) - [Churn Time Frame Value(s)] && DIM_Date[Date] < MIN ( DIM_Date[Date] ) ) ) RETURN CALCULATE ( SUM ( FACT_Actuals[VenueNumber] ), FILTER ( FACT_Actuals, FACT_Actuals[ClientID] IN EXCEPT ( CustomerTM, PriorCustomers ) ) )

topic Re: Calculating new venue acquisitions/renewals based on a user assignable "last acquired date" in DAX Commands and Tips

Calculating new venue acquisitions/renewals based on a user assignable "last acquired date"

Re: Calculating new venue acquisitions/renewals based on a user assignable "last acquired date"