Power BI - DAX Measure 计算当前期间流失和重新激活的客户.总数不正确 [英] Power BI - DAX Measure to calculate churned and reactivated customers in the current period. Incorrect total

问题描述

以下是数据的简化版本.客户 ID 的每日交易清单

Below is a simplified version of the data. Daily transaction list for customer ID

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 }
    }
)

目标:计算当前期间(在以下示例中为 2019 年 4 月 1 日至 7 日)内重新激活和流失的客户.

Objective: Calculate Reactivated and churned customers during the current period which in the example below is 1-7th April 2019.

流失 = 90 天或更长时间不活跃.

Churned = Inactive for 90 days or more.

重新激活 = 在最近一次购买前 90 天或更长时间处于非活动状态.

Reactivated = Inactive for 90 days or more prior to making the latest purchase.

在矩阵中 - 如下图所示 - 以下措施在当前期间(4 月 1 日至 7 日)中按预期工作.

In a matrix - as visualized below - the following measures work as expected for reactivated and churned in the current period, 1st to 7th of April.

    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 天，也应该是动态的.

Please note that the "current period" in this case needs to be dynamic to the date, thats why the date slicer is there and I use an allexpect on the date column to make it work. In the if statement it's 90 + 7 days, should be dynamic this as well.

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)

如屏幕截图所示，矩阵按预期工作.不是总数.我尝试使用带有 distinctcount 的计算来相应地计算客户，但没有成功.目前，我通过在 excel(!) 中导出矩阵和求和来在我的真实数据集中解决这个问题.

As the screenshot show the matrix works as expected. Not the totals. I've tried using calculate with distinctcount to count the customers accordingly without success. Currently I solve this in my real dataset by exporting the matrix and sum in excel(!).

必须是一种更好的方式来使用 DAX.

Has to be a better way to make this work with DAX.

非常感谢您的帮助.

推荐答案

首先，您需要一个与您的 SalesData 表没有关系的 Dates 表用作切片器.以下内容足以满足此处的目的:

First, you need a Dates table with no relationship to your SalesData table to use as a slicer. The following works well enough for purposes here:

Dates = CALENDAR( DATE( 2018, 1, 1 ), DATE( 2019, 12, 31 ) )

当您将其用作切片器时，您可以读取最大和最小日期以获得如下动态周期:

When you use that as a slicer, you can read the max and min dates to get a dynamic period like this:

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 )

这并不能解决小计问题，但您现在可以编写一个使用上述方法的新度量:

This doesn't solve the subtotal problem, but you can now write a new measure that uses the above that does:

ChurnedCount = SUMX ( VALUES ( SalesData[Customer ID] ), [ChurnedInPeriod] )

重新激活帐户的方法应该类似.

The approach for reactivated accounts should be similar.

这里的关键是您需要分别为每个客户评估 ChurnedInPeriod，而这正是 ChurnedCount 所做的.对于每个单独的客户，它会评估该客户的 ChurnedInPeriod，然后将它们加在一起.这种 SUMX ( VALUES( ... ), ... ) 模式对于需要从较低粒度计算中汇总的小计很常见.

The key here is that you need to evaluate ChurnedInPeriod for each customer separately and that's exactly what ChurnedCount does. For each individual customer, it evaluates ChurnedInPeriod for that one and then adds them all together. This SUMX ( VALUES( ... ), ... ) pattern is common for subtotals that need to be rolled up from lower granularity calculations.

这是 另一个问题，讨论 SUMX ( VALUES ( ... ), ... )并包含更多链接.

Here's another question that discusses SUMX ( VALUES ( ... ), ... ) and include further links.

这篇关于Power BI - DAX Measure 计算当前期间流失和重新激活的客户.总数不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！