在业务分析中,关于如何计算上个订单日期的相关指标,经常被人问到,这里就来介绍一下如何用PowerBI来实现这种分析。
以这个简单的数据为例:
如何找出相同产品每笔订单的上一笔的订单日期呢?这里通过M和DAX几种方式来实现这个需求。
利用M添加自定义列
在PowerQuery中添加自定义列:
上个订单日期=
List.Max(
Table.SelectRows(
更改的类型,
(x)=>
x[产品名称]=[产品名称] and
x[订单日期]<[订单日期]
)[订单日期]
)
利用DAX添加计算列
在数据视图中添加计算列:
上个订单日期 =
VAR a=[产品名称]
VAR b=[订单日期]
VAR c=
CALCULATE(
MAX('数据表'[订单日期]),
FILTER('数据表','数据表'[产品名称]=a&&'数据表'[订单日期]<b)
)
RETURN c
利用DAX创建度量值
上面都是添加列的方法,还可以利用度量值来实现,在上下文不变的情况下,度量值的写法也与计算列类似:
上个订单日期 度量值 =
VAR a=MAX([产品名称])
VAR b=MAX([订单日期])
VAR c=
CALCULATE(
MAX('数据表'[订单日期]),
FILTER(ALL('数据表'),'数据表'[产品名称]=a&&'数据表'[订单日期]<b)
)
RETURN c
上面几种方式,无论是M还是DAX,无论是添加列还是度量值,其实逻辑是一样的。
都是先筛选出产品名称等于当前行的产品名称、并且订单日期小于当前当前行订单日期的所有行(M函数利用 Table.SelectRows筛选,DAX利用FILTER函数筛选),然后计算筛选结果的最大订单日期,也就是上一个订单日期。
现在有了窗口函数,其实利用窗口函数计算更加简单,比如利用OFFSET函数来写个度量值:
上个订单日期 OFFSET =
CALCULATE(
MAX('数据表'[订单日期]),
OFFSET(
-1,
ALLSELECTED('数据表'[订单日期],'数据表'[产品名称]),,,
PARTITIONBY('数据表'[产品名称]))
)
将这个度量放到上面的可视化表格中,结果与前面的做法完全一样。
关于窗口函数的用法可以参考:
Power BI本月正式推出的DAX新函数:OFFSET、INDEX、WINDOW
PowerBI分析技巧:利用窗口函数进行帕累托分析,大幅提升性能
以上就是计算上个订单日期的几种方式,有了它就可以进一步计算上个订单日期的数据以及订单间隔天数等,也可以进行上一行下一行的相关计算,灵活运用可以解决各种类似的问题。
PowerBI星球