之前白茶曾经分享过《同比环比问题》,但是在实际需求中还远远不能满足实际的需要。为什么呢?说来也很简单,假如我开了一个小店,要求每日观测“日环比”,但是日期中间有挂蛋。什么叫挂蛋,就是没有销售的情况,这种情况下再使用之前的方法很显然是不合理的。那么该如何进行呢?
来,跟着白茶的思路走。
这是本期白茶所使用的一组示例文件,将其导入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的初学者。
下面这个知识星球是针对有实际需求的小伙伴,有需要的请加入下面的知识星球。