使用新的 DAX 窗口函数计算客户终身价值

本文翻译自Nikola Ilic的文章—《Looking through the Window— Calculating customer lifetime value with new DAX functions》来源：Medium 新的窗口函数是DAX语言有史以来最重要的改进之一! 学习如何利用它们来计算客户终身价值。

之前我作为SQL专家的时期，一直在频繁使用T-SQL窗口函数来完成各种分析任务。这篇文章介绍了其中一个可能遇到的分析情境，但实际上有十几种情境可以通过使用窗口函数快速而直观地解决。

因此，当我过渡到Power BI时，我非常惊讶（不能说是失望），因为没有相当于SQL窗口函数的DAX。好吧，我们也可以通过编写更复杂的DAX来解决对某一组行进行不同计算的挑战--但是，说实话，这往往是一个非常痛苦的经历。

因此，当Power BI Desktop 2022年12月的更新宣布了一组全新的DAX函数--统称为窗口函数--应该能达到与SQL窗口函数相同的目标时，我感到无比兴奋。目前，有三个DAX窗口函数。OFFSET，INDEX，和WINDOW。

如果你想更多地了解这些函数以及它们在幕后是如何工作的，我强烈推荐你阅读Jeffrey Wang的这篇文章(文章地址：https://pbidax.wordpress.com/2022/12/15/introducing-dax-window-functions-part-1/)--这是深入了解DAX窗口函数的最佳启蒙。

如果你对OFFSET功能特别感兴趣，我鼓励你阅读我的朋友Tom Martens写的这篇好文章（文章地址：
https://www.minceddata.info/2022/12/14/unlock-an-ample-new-world-by-seeing-through-a-window/）。

或者Štěpán Rešl写的这篇（文章地址：
https://www.linkedin.com/pulse/offset-its-usage-calculation-groups-%C5%A1t%C4%9Bp%C3%A1n-re%C5%A1l/）。

在这篇文章中，我不会花太多时间解释窗口函数的来龙去脉，因为我想重点解释你如何利用这些函数来满足一个非常普遍的业务要求--计算客户的终身价值。

客户终身价值

让我们首先了解什么是客户终身价值。嗯，这是一个宽泛的术语，可以有许多可能的解释。在我们的案例中，我们想深入了解单个客户的行为--例如，他们下了多少订单，他们在我们的产品上花费的总金额是多少，他们的订单之间有多少天的间隔，以及这与所有客户的平均值相比如何。最后，我们想知道我们的客户有多忠诚--也就是说，他们的第一个和最后一个订单之间有多少天的间隔。

因此，让我们从一个非常基本的场景开始。我正在分析Adventure Works数据集中的两个客户。Adam Young和Alexandra Jenkins。下面是他们的订单和总销售金额的摘要。

这里要理解的第一个概念是，我们要把每个客户作为一个单独的 "实体 "来对待--这意味着，我们要为每个客户创建一个 "窗口"，并分析这一组特定行的数字。在我们的案例中，我们将有两个 "窗口"。

DAX中的窗口函数可以以两种不同的方式工作--1.通过基于当前行的相对（REL）值进行操作（阅读Jeffrey的文章可以了解如何确定当前行），2.通过绝对（ABS）值进行操作。在我的案例中，我总是希望我的窗口从分区的第一行开始（每个客户代表一个单独的分区），并在分区的最后一行结束。

因此，让我们创建一个度量值，计算每个分区的订单数量和销售额的运行总量。

Window Quantity = CALCULATE([Total Quantity],
                   WINDOW(
                        1,ABS,
                        -1,ABS,
                        SUMMARIZE(ALLSELECTED(Sales),Customer[Customer],'Date'[Date]),
                        ORDERBY ('Date'[Date]),
                        KEEP,
                        PARTITIONBY(Customer[Customer])
                   )
                   )

Window Sales Amount = CALCULATE([Total Sales],
                   WINDOW(
                        1,ABS,
                        -1,ABS,
                        SUMMARIZE(ALLSELECTED(Sales),Customer[Customer],'Date'[Date]),
                        ORDERBY ('Date'[Date]),
                        KEEP,
                        PARTITIONBY(Customer[Customer])
                   )
                   )

让我们停一下，解释一下这个度量的定义。作为一个CALCULATE筛选器的修改器，我们将使用一个新的窗口DAX函数WINDOW。第一个参数（1）决定了窗口的起始位置。因为第二个参数是ABS（绝对值），这意味着窗口从分区的起点开始。接下来，我们定义窗口的结束位置。

由于我们使用的是一个负值（-1）和ABS，这意味着窗口在分区的最后一行结束。之后，我们要定义一个表的表达式，输出行将从中返回。最后，数据将在窗口内按日期排序（从最早的日期开始），并在一个客户上进行分区（每个客户是一个单独的 "窗口"）。

现在，我们有了每个客户的累积总计! 因此，我们现在可以计算出每个人在整个购买中的百分比。

% of Window Quantity = DIVIDE([Total Quantity],[Window Quantity],0)
% of Window Sales = DIVIDE([Total Sales],[Window Sales Amount],0)

到目前为止，一切都很好! 让我们做一些更酷的事情。首先，我将计算两个连续订单之间相隔多少天。对于这个任务，另一个DAX窗口函数来了。OFFSET。我想抓取前一行的日期，并计算当前行日期与前一行日期之间的天数差。

Offset Date = CALCULATE(
                        MIN('Date'[Date]),
                        OFFSET(
                            -1,
                            SUMMARIZE(ALLSELECTED(Sales),Customer[Customer],'Date'[Date]),
                            ORDERBY('Date'[Date]),
                            KEEP,
                            PARTITIONBY(Customer[Customer])
                        )
                        )

这里是计算订单之间的天数的度量。

Days Between Orders = DATEDIFF([Offset Date],MIN('Date'[Date]),DAY)

好了，现在，让我们计算一下每个客户的平均订单间隔天数。

AVG Days Between Orders = CALCULATE(AVERAGEX(
                  SUMMARIZE(
                            ALLSELECTED(Sales),Customer[Customer],'Date'[Date]),[Days Between Orders]),
                   WINDOW(
                        1,ABS,
                        -1,ABS,
                        SUMMARIZE(ALLSELECTED(Sales),Customer[Customer],'Date'[Date]),
                        ORDERBY ('Date'[Date]),
                        KEEP,
                        PARTITIONBY(Customer[Customer])
                   )
                   )

那么，在这一点上我们能得出什么结论呢？Alexandra平均每22天下一次订单，而Adam平均需要89天才能下一次新订单。这与一个整体相比如何？89天的订单间隔是不是太长了？

因此，让我们把这些数字放在整个数据集的背景中。

尽管与Alexandra Jenkins的22天相比，89天可能看起来很糟糕，但我们可以得出结论，与所有客户的平均水平（287天）相比之下，这89天一点也不糟糕！这也是我们的目标。这的确是一个有价值的洞察。

让我们通过计算总的客户寿命来结束这个分析。即每个客户的第一个和最后一个订单之间的天数。因此，让我们在我们的 "窗口 "内计算出第一个和最后一个订单的日期。

Window Min Date = CALCULATE(MIN('Date'[Date]),
                   WINDOW(
                        1,ABS,
                        -1,ABS,
                        SUMMARIZE(ALLSELECTED(Sales),Customer[Customer],'Date'[Date]),
                        ORDERBY ('Date'[Date]),
                        KEEP,
                        PARTITIONBY(Customer[Customer])
                   )
                   )

Window Max Date = CALCULATE(MAX('Date'[Date]),
                   WINDOW(
                        1,ABS,
                        -1,ABS,
                        SUMMARIZE(ALLSELECTED(Sales),Customer[Customer],'Date'[Date]),
                        ORDERBY ('Date'[Date]),
                        KEEP,
                        PARTITIONBY(Customer[Customer])
                   )
                   )

下面是客户寿命天数的计算。

Customer Lifetime Days = DATEDIFF([Window Min Date],[Window Max Date],DAY)
而且，一旦我把它拖入表格，我可以看到它在每个窗口内被计算（分别为每个客户）。

总结

这只是一个基本的例子，说明使用DAX窗口函数可以实现什么。老实说，可以想到的用例不计其数--例如，我还可以对窗口内的各个行进行排序（并快速确定每个客户的最高销售金额）。

我还可以通过多个属性进行分区--例如，通过客户和月份。然后，我们会有一个 "窗口"，包含一个客户在一个月内的所有行。例如Adam Young-七月，Adam Young-八月，Alexandra Jenkins-七月，Alexandra Jenkins-八月，等等。根据你的具体业务要求，你的 "窗口 "可以有不同的定义。

窗口函数是DAX语言最重要的增强功能之一，这一点是毫无疑问的。一些以前需要编写复杂和冗长的DAX的业务用例，现在可以用一种更优雅和最佳的方式来完成。就像在SQL语言中，窗口函数是最强大的分析工具之一一样，DAX窗口函数肯定会使Power BI的许多开发任务更容易实现

---

如果您想深入学习微软Power BI，欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号（PowerPivot工坊）后猛戳”在线学习”。

长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯，欢迎小伙伴儿们转发分享~

Power Pivot工坊