Hello! Hopefully, somebody could help me. Faced a problem when executing DAX formula "Temporarily Lost Customers" using DAX Patterns guide "New and Returning Customers". Temporarily Lost Customers are those who have purchased smth in a period prior to the current (e.g. previous month) but have no purchases within the current period. Some of them are also considered to be completely lost. The issue is that for the first month of the selected period in slicer ( for August in period 08/01/2021 - 12/31/2021) it calculates the number of Temporarily Lost Customers incorrectly (her efor August), reducing it by the number of Completely Lost Customers. However, when I chose a wider period (e.g. 07/01/2021 - 12/31/2021) this measure includes the number of completely lost customers. Maybe somebody could help me amend this formula so it would work correctly for all the selected period. Thanks in advance!
These are the DAX formulas used:
Date Temporary Lost Customer =
VAR MaxDate =
CALCULATE ( -- The last sale is the MAX of Order Date
MAX ( 'cft_operations'[action_date]), -- in the current period (set by the calling measure)
ALLEXCEPT (
'cft_operations', -- ignoring any filter
-- other than Date
--'cft_operations'[action_date],
'cft_operations'[user_id],
'cft_operations'[activity_type],
'cft_operations'[partner_id],
'_Calendar',
'partners',
--and Customer
'cft_users'
)
)
VAR Result =
IF (
NOT ISBLANK ( MaxDate ),
EOMONTH ( MaxDate, 1 )
)
RETURN
Result
Temporarily Lost Customers =
VAR MinDate = MIN ( '_Calendar'[Date_ID])
VAR CustomersWithLostDateComplete =
CALCULATETABLE ( -- Prepares a table that
ADDCOLUMNS ( -- for each customer contains
VALUES ( 'cft_operations'[user_id]), -- the temporarily-lost date
"@TemporarilyLostCustomerDate", CALCULATE (
[Date Temporary Lost Customer],
'_Calendar'[Date_ID] < MinDate
)
), -- ignoring any filter
ALLSELECTED(cft_users),
ALLSELECTED('_Calendar') -- other than Customer
)
VAR CustomersWithLostDate =
FILTER ( -- Removes the customers without a
CustomersWithLostDateComplete, -- temporarily-lost date
NOT ISBLANK ( [@TemporarilyLostCustomerDate] )
)
VAR PotentialTemporarilyLostCustomers =
FILTER (
CustomersWithLostDate, -- Filters the customers
[@TemporarilyLostCustomerDate] -- whose lost-customer date
IN VALUES ( '_Calendar'[Date_ID]) -- falls within the current period
)
VAR ActiveCustomers =
ADDCOLUMNS ( -- Gets the first order date of
VALUES ( 'cft_operations'[user_id]), -- customers in the current selection
"@MinOrderDate", CALCULATE ( MIN ( 'cft_operations'[action_date]) )
)
VAR TemporarilyLostCustomers =
FILTER ( -- Filters the temporarily-lost
NATURALLEFTOUTERJOIN ( -- customers by combining
PotentialTemporarilyLostCustomers,-- potential lost customers
ActiveCustomers -- and active customers
), -- and then comparing dates
OR (
ISBLANK ( [@MinOrderDate] ),
[@MinOrderDate] > [@TemporarilyLostCustomerDate]
)
)
VAR Result =
COUNTROWS ( TemporarilyLostCustomers )
RETURN
Result
Determining lost customers
