Power BI — измерение DAX для подсчета ушедших и повторно активированных клиентов за текущий период. Неверная сумма
Ниже представлена упрощенная версия данных. Ежедневный список транзакций для идентификатора клиента
SalesData =
DATATABLE (
"Customer ID", INTEGER,
"Date", DATETIME,
"Amount", INTEGER,
{
{ 101245, "2019/04/07", 500 },
{ 101245, "2018/08/05", 400 },
{ 100365, "2018/07/30", 900 },
{ 100365, "2018/02/22", 700 },
{ 104300, "2019/04/05", 300 },
{ 104300, "2019/04/03", 350 },
{ 104300, "2019/04/01", 310 },
{ 107804, "2018/11/08", 650 },
{ 107804, "2018/11/19", 640 },
{ 108040, "2019/01/02", 730 }
}
)
Цель: подсчитать количество повторно активированных и ушедших клиентов в течение текущий период, которое в приведенном ниже примере равно 1-7 апреля 2019 г..
Churned = неактивен в течение 90 дней или более.
Повторно активировано = неактивно в течение 90 или более дней до совершения последней покупки.
В матрице, как показано ниже, следующие меры работают, как и ожидалось, для повторно активированного и измененного в текущем периоде с 1 по 7 апреля.
churnedInCurrentPeriod =
VAR dayspassed =
DATEDIFF(
MAX(SalesData[Date]),
CALCULATE(
MAX(SalesData[Date]),
ALLEXCEPT(SalesData,SalesData[Date])),
DAY)
Return
IF(dayspassed >= 90 && dayspassed <= 97,1,0)
Обратите внимание, что «текущий период» в этом случае должен быть динамическим по отношению к дате, поэтому существует слайсер даты, и я использую allexpect в столбце даты, чтобы он работал. В операторе if это 90 + 7 дней, это также должно быть динамическим.
ReactivatedInCurrentPeriod =
VAR differenceDays =
DATEDIFF(
CALCULATE(
MAX(SalesData[Date]),
FILTER(SalesData,SalesData[Date] <> MAX(SalesData[Date])
)
),
MAX(SalesData[Date]),
DAY
)
RETURN
IF(AND(differenceDays >= 90,MAX(SalesData[Date]) >= DATE(2019,4,1)),1,0)
Как видно из скриншота, матрица работает должным образом. Не итоги. Я безуспешно пытался использовать вычисление с Differentcount для подсчета клиентов соответственно. В настоящее время я решаю это в своем реальном наборе данных, экспортируя матрицу и сумму в excel (!).
Должен быть лучший способ заставить это работать с DAX.
Большое спасибо за помощь.
Ответы 1
Во-первых, вам нужна таблица Dates, не связанная с вашей таблицей SalesData, чтобы использовать ее в качестве слайсера. Следующее работает достаточно хорошо для целей здесь:
Dates = CALENDAR( DATE( 2018, 1, 1 ), DATE( 2019, 12, 31 ) )
Когда вы используете это как слайсер, вы можете прочитать максимальную и минимальную даты, чтобы получить такой динамический период:
ChurnedInPeriod =
VAR MaxDate = MAX ( Dates[Date] )
VAR MinDate = MIN ( Dates[Date] )
VAR CustomerLastDate = CALCULATE ( MAX ( SalesData[Date] ), SalesData[Date] <= MaxDate )
VAR DaysPassed = MaxDate - CustomerLastDate
VAR PeriodLength = MaxDate - MinDate
RETURN
IF ( DaysPassed >= 90 && DaysPassed <= 90 + PeriodLength, 1, 0 )
Это не решает проблему с промежуточными суммами, но теперь вы можете написать новую меру, которая использует описанное выше:
ChurnedCount = SUMX ( VALUES ( SalesData[Customer ID] ), [ChurnedInPeriod] )
Подход к повторно активированным учетным записям должен быть аналогичным.
Ключевым моментом здесь является то, что вам нужно оценивать ChurnedInPeriod для каждого клиента отдельно, и это именно то, что ChurnedCount делает. Для каждого отдельного клиента он оценивает ChurnedInPeriod для этого, а затем суммирует их всех вместе. Этот шаблон SUMX ( VALUES( ... ), ... ) распространен для промежуточных итогов, которые необходимо свернуть из вычислений с более низкой степенью детализации.
Вот Другой вопрос, в котором обсуждается SUMX ( VALUES ( ... ), ... ) и есть дополнительные ссылки.
Аккуратное решение! работает очень хорошо. СУММ (ЗНАЧЕНИЯ... - отличное предложение для решения подобных проблем. Я буду использовать его в будущем. Спасибо)