Power BI-DAX度量来计算当前期间的流失和重新激活的客户。总计不正确 [英] 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日的当前期间重新激活并 / strong>。

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)

如屏幕截图所示，矩阵按预期工作。不是总数。我尝试使用带有不重复计数的计算来对客户进行相应计数，但没有成功。目前，我通过导出矩阵和总和在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.

非常感谢您的帮助。

推荐答案

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

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

重新激活帐户的方法ts应该相似。

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度量来计算当前期间的流失和重新激活的客户。总计不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！