search

PowerBI-tips / Deneb-Templates

Location for sharing templates for the deneb Power BI custom visual
126 stars 39 forks source link

deneb-template-customer-retention #3

Open ThysvdW opened 2 years ago

ThysvdW commented 2 years ago

Attached the customer retention deneb template as requested. Based upon the following https://www.workout-wednesday.com/2018/05/01/week-18/

Created Calculated Columns for :

Cohort = 
VAR __Year = YEAR ( 'Customers'[First Order] )

VAR __Quarter = QUARTER( 'Customers'[First Order] )

RETURN
__Year & " - Q" & __Quarter
User Retention = 
VAR __FirstOrder = 
LOOKUPVALUE( Customers[First Order], Customers[Customer ID], Orders[Customer ID] )

VAR __OrderDate =
Orders[Order Date]

VAR __DateDiff =
DATEDIFF ( __FirstOrder, __OrderDate, QUARTER )

RETURN
__DateDiff

Created the following measures:

# Customers = DISTINCTCOUNT( Orders[Customer ID] )
Retention% = 
VAR __TotalCustomers =
CALCULATE(
    [# Customers],
    ALL ( Orders[User Retention] )
)

VAR __CustomersRetained =
[# Customers]

RETURN
    DIVIDE( __CustomersRetained, __TotalCustomers )

customer-retention.txt

MikeCarlo commented 2 years ago

thank you I will get the template merged into the code base