如何用Power BI进行工作日相关的计算?

我们平时应该都会碰到针对工作日的分析,在Power BI中,并没有与工作日相关的函数,很多人因此就无从下手,不知道该如何计算了,那么,如何用PowerBI处理工作日的分析呢?这篇文章给你提供个思路。

考虑到非工作日,不仅有周末,还有法定节假日,并且涉及到调休等问题,很难有某个固定的函数,进行这些不规则的个性化计算,即使Excel中有工作日函数,也并不方便,直接使用的计算结果也很可能与实际不符。

其实最简单的办法就是先做一个节假日的列表,你可以在网上找每年节假日的资料,即使是手工制作,也不麻烦,毕竟周末是很规律的,法定节假日和调休的处理每年也就是十几天而已,很快就可以做出来这张表。

这里我先做出一个2019年到2021年的中国节假日表:

然后将这个假日表导入到PowerBI数据模型中,这里不将它与模型中的其他表建立关系,而是利用它,在模型原有的日期表中,添加关于工作日、节假日的字段。

首先判断每个日期是否为工作日,添加一列工作日标记:

工作日标记 =
IF(
LOOKUPVALUE(
'中国节假日表'[日期],
'中国节假日表'[日期],
[日期]
),
0 ,  1
)
这里用了DAX函数LOOKUPVALUE,它不需要两个表建立关系即可查找,如果在假日表中找到日期,返回0,否则返回1,1代表是工作日。

关于LOOKUPVALUE的语法和用法,可以参考下图,这里不再介绍:

同样的方式,利用LOOKUPVALUE,将每个日期对应的节假日名称和节假日类别匹配过来,如果不是节假日,就返回空白:

节假日名称 =

LOOKUPVALUE(

'中国节假日表'[节假日名称],

'中国节假日表'[日期],

[日期]

)

节假日类型 =

LOOKUPVALUE(

'中国节假日表'[节假日类型],

'中国节假日表'[日期],

[日期]

)

在日期表中显示如下(节选):

为了能够灵活进行工作日的计算,这里我再添加一个字段工作日编号,为每个工作日添加一个不重复的顺序索引。

工作日编号 =

IF(

[工作日标记]=1,

CALCULATE(

SUM('日期表'[工作日标记]),

FILTER(ALL('日期表'),[日期]<=EARLIER('日期表'[日期]))

)

)

这个工作日编号非常好用,下面会看到它的用处。

至此,完成对日期表的补充构造,下面就可以开始工作日的相关计算了。

统计某期间的工作日天数

计算两个日期之间的工作日的天数,只需要这样写一个度量值:

工作日天数 = SUM('日期表'[工作日标记])

是不是非常简单,这就是工作日标记用0和1的好处。

上/下个工作日

如果想找出某个日期的上一个工作日,就可以这样写:

上个工作日 =

CALCULATE(

MAX('日期表'[日期]),

FILTER(

ALL('日期表'),

'日期表'[日期]<MAX('日期表'[日期])&&

'日期表'[工作日标记]=1

)

)

这个度量值的逻辑就是找小于当前日期,并且工作日标记为1的最大日期,也就是上一个工作日。

同理,下一个工作日的写法:

下个工作日 =

CALCULATE(

MIN('日期表'[日期]),

FILTER(

ALL('日期表'),

'日期表'[日期]>MAX('日期表'[日期])&&

'日期表'[工作日标记]=1

)

)

效果如下:

N个工作日后是哪一天

日常经常会遇到某些事项的办结期限是10个工作日、15个工作日等,那么如何快速计算出N个工作日后,是哪一天呢?

这样的计算需要先建立一个参数,然后写个度量值:

这里就用到了日期表中添加的工作日编号,通过这个编号的加减,就可以快速定位到N个工作日后是哪一天。

这里的参数我也设置了负数,如果是-10,实际上是10个工作日前是哪一天。

以上就是关于工作日的几种常见计算,有了日期,再计算这些日期对应的业务数据就很简单了。

上面的计算并没有专门的函数,我们只需要掌握工作日的逻辑常识,配合常用的DAX函数就可以实现各种工作日相关的分析需要,其中的关键是,先完善带有假日标记的日期表。

这种做法,和之前介绍的关于周的分析,思路是一致的:学会了这个思路,你也可以轻松进行周分析!

当然本文构造的日期表中不仅有工作日,还有各种节假日字段,进行假日的分析同样很便捷,比如想比较每一年春节对应的销售数据,只需要一个切片器就可以了。


PowerBI商业数据分析

帮你从0到1,轻松上手PowerBI

(0)

相关推荐

  • 既然Power Pivot表间建关系后可直接调数据,还要Lookupvalue这个函数干嘛?

    小勤:在Power Pivot里对两个表建立了关系之后,就可以直接调用相关表的数据了,那还要Related或Lookupvalue这个函数干嘛呢? 大海:这个问题好难回答.不过,举个实际工作中的例子吧 ...

  • 早下班技巧:Excel日期标记非工作日

    Excel日期标记非工作日=mod(A1-2,7)>4

  • 如何用Power BI分析产品关联度?

    逛超市的时候,面对货架上琳琅满目的商品,你会觉得这些商品的摆放,或者不同品类的货架分布是随机排列的吗,当然不是. 应该都听说过啤酒与尿布的故事,这两个表面上毫不相关的商品,在超市中摆放在一起时二者的销 ...

  • 如何用Power BI计算在职员工数量?

    经常碰到的一类问题是,如何根据起止日期来计算某个时间点的数量,比如: 已知合同的生效日期和到期日期,特定日期的有效合同有多少? 已知店铺的开业日期和注销日期,截至目前正在运营的店铺有多少? 已知员工的 ...

  • 如何用Power BI设计复杂结构的表格?

    复杂结构的表格,一般是指多行表头.特殊维度的表格,也被称为中国式报表,其实不论国内国外,都会有这种需求,这篇文章就来看看如何在PowerBI中,制作这样的表格. 如果只是现有维度结构的多行表头,那很简 ...

  • Power BI非标准日历的计算思路

    对于按照自然年月日来分析的业务数据,在PowerBI中可以轻松的使用时间智能函数来进行各种时间指标的计算,但如果不是按标准的日历,很多人就开始有点懵,不知道该如何计算了. 比如有的公司的业务月份是从2 ...

  • Power BI财务报表分析:指标计算篇

    PowerBI财务报表分析系列之: 指标计算篇 财务报表分析,涉及到众多的财务指标计算,在PowerBI中利用度量值,计算出的不仅仅是一个个静态的指标数据,并且还可以和切片器配合,快速的完成不同公司. ...

  • Power BI导出数据到Excel的方法及相关限制

    前些天,有朋友留言说:Power BI里导出数据只能30000行,导出的数据不完整,怎么办-- 我其实很少直接在Power BI里导出如此大量的数据,所以,我原来并没有注意到这个限制. 现在刚好有人问 ...

  • PBI-基础入门:通过一个简单的案例体会Power BI的数据分析过程

    小勤:用个例子给我讲讲Power BI怎么用的呗. 大海:好吧,Power Query你学了那么多,Power Pivot也基本入门了,Power BI也可以同步开始学了,反正PQ和PP的知识除了操作 ...

  • PBI-基础入门:Power BI是什么?

    按照微软的艰涩定义(不喜欢看的可直接略过本段):Power BI 是软件服务.应用和连接器的集合,它们协同工作以将相关数据来源转换为连贯的视觉逼真的交互式见解.无论你的数据是简单的 Excel 电子表 ...

  • PBI-基础入门:Power BI的基本组成

    Power BI主要有3大块产品组成: PowerBI Desktop:即数据分析人员用来做数据处理.构建模型和数据分析的客户端,是数据模型和分析的设计器. PowerBI Service:即经过Po ...