TOPN函数丨环比断点

之前白茶曾经分享过《同比环比问题》，但是在实际需求中还远远不能满足实际的需要。为什么呢？说来也很简单，假如我开了一个小店，要求每日观测“日环比”，但是日期中间有挂蛋。什么叫挂蛋，就是没有销售的情况，这种情况下再使用之前的方法很显然是不合理的。那么该如何进行呢？

来，跟着白茶的思路走。

这是本期白茶所使用的一组示例文件，将其导入PowerBI中。

（示例文件会放到知识星球中，小伙伴们可以搜索“PowerBI丨需求圈”，文章结尾处会有二维码。）

结果如下：

并生成日期表，这里就不赘述了。

先给小伙伴们看看要是使用之前的办法是怎么运算的：

基本代码：

销售 =
SUM ( '示例'[销售金额] )

按照CALCULATE的经典模式编写如下代码：

上期 =
CALCULATE ( [销售], DATEADD ( '日期表'[Date], -1, DAY ) )

环比代码：

环比 =
DIVIDE ( [销售] - [上期], [上期] )

结果如图：

小伙伴们，发现问题没？我有很多日期都是没有销售的，也就是属于挂蛋的，比如1月3日就是没有销售，这种情况下我需要的是4日的销售直接与3日的销售进行环比，忽略3日这一天，该如何进行呢？

（写在前面的话：感谢@焦棚子大师，白茶也是借鉴了大师的方法，喜欢焦大师的可以在知乎搜索“焦棚子”。）

这个时候就需要TOPN函数了：

语法=
TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

第一参数表示要返回的数值，比如说返回前3，前2，前5，这种。需要是一个标量值。

第二参数是被执行的表。

第三参数是对表进行排序的依据。

第四参数（可选），设定升降序。

需要注意的是：如果有相同的数据，返回的结果是大于N值的。

比如说，我想返回前五名，但是第四第五是一样的分数，那么返回的结果就是前六名。

编写如下代码：

TOPN日环比 =
IF (
    HASONEFILTER ( '示例'[时间] ),
    VAR BCMAX =
        MAX ( '示例'[时间] )
    VAR TP =
        TOPN ( 1, FILTER ( ALL ( '示例'[时间] ), '示例'[时间] < BCMAX ), '示例'[时间], DESC )
    VAR LS =
        CALCULATE ( '示例'[销售], '示例'[时间] = TP )
    RETURN
        IF ( LS <> BLANK (), DIVIDE ( '示例'[销售] - LS, LS ), BLANK () )
)

结果如图：

这次的结果没有任何问题。

这里解释一下各段代码含义：

IF+HASONEFILTER的组合是为了让总计栏不显示合计，因为我们查看的是每日环比的结果，看总计没啥意义。

BCMAX这里是为了获取事实表中的最大日期。

TP这里分段说一下。

1是返回第一行；返回小于最新日期的前一天；排序依据是事实表日期这一列；DESC是为了保证按照降序的顺序排列。

LS这里是求出前一天的销售额。

最后，利用IF判断前一天的营业额是否为空，为空就跳过，否则就显示环比结果。

这样就得到了我们需要的结果。

* * *

小伙伴们❤GET了么？

白茶会不定期的分享一些函数卡片

（文件在知识星球PowerBI丨需求圈）

这里是白茶，一个PowerBI的初学者。