Power BI之DAX神功：第4卷第13回计算两个日期之间的工作日数量

孙兴华

教育领域答主

一、Excel中计算两个日期之间的工作日天数

从微软官网看到NETWORKDAYS函数可以在office365和Excel2021中找到，其它版本是否可以使用需要自行测试。

语法：NETWORKDAYS(开始日期，结束日期，【国家法定假日】)

第1参、第2参必选，第3参数可选（可以是1个单元格，也可以是一个区域）

假设：2021/10/7的休息需要在2021/10/9来补班，那我们就应该筛选补班为空的，得到法定假日列。如下图所示。国家法定假日和补班一定是国家最权威机构发出来的，任何搜索引擎都能搜到。 Excel里面想实现法定假日和补班，你还要动动脑子，因为我们今天不是讲Excel，我就省事来做了。既然DAX学到这里了，也就不用Excel工作表函数去分析了。

Excel公式：

=NETWORKDAYS(A2,B2)   // 返回：21   因为总计31天，周六日占了10天
=NETWORKDAYS(A2,B2,I1)  //返回：20  因为总计31天，周六日占了10天，10月1日休息一天
=NETWORKDAYS(A2,B2,I2:I14) // 返回：17   因为总计31天，法定假日6天-2=4天，周六日原本占10天，31-4-10=17天

二、使用DAX实现（请举一反三）

《权威指南》这节课讲的非常好，带着你从计算两个日期间隔天数开始还对同一个度量值提供了不同的写法和极限优化。书上讲的没毛病，感兴趣可以看。由于国内外情况不同，且极限优化也是不是我的菜。我就讲我平时怎么做的。今天我讲的节假日，你可以举一反三，例如你是电商、超市、医院、公交等，你哪来法定节假日？你们牺牲了所有法定节假日为我们提供服务，我就讲讲分析方法来回报你们。有些行业我不懂，但是拿电商打比方，你们经常有活动，比如三八女神节，618狂欢购，双11，双12等等，这些档期都是你们的节日。你们可以利用今天的方法计算这些档期期间的销售。

【1】请到最权威网站下载法定节假日和补休日期

请到 中国政府网 官网右上角搜索：

这样得到的节假日安排肯定是正确的，但是可能后期会有调整，以 中国政府网 为准。

【2】制作法定假日表和补休表

【3】将销售表、假日表、补休表都导入PowerBI

【4】制作日期表

友情提示：因为要用到星期几，按国内规则，这次的动态日期表用 WEEKDAY([Date],2) 从星期一开始算第1天。 详见《DAX神功》第2卷第17回

日期表 = ADDCOLUMNS( 
CALENDAR(FIRSTDATE('销售表'[日期]),LASTDATE('销售表'[日期])),
"年", YEAR ( [Date] ),
"季度", ROUNDUP(MONTH([Date])/3,0),
"月", MONTH([Date]),
"周", weeknum([Date]),
"年季度", year([date]) & "Q" & ROUNDUP(MONTH([Date])/3,0),
"年月", year([Date]) * 100 + MONTH([Date]),
"年周", year([Date]) * 100 + weeknum([Date]),
"星期几", WEEKDAY([Date],2) 
)

【5】为日期表新建列（下面3个公式都是新建列）

是否上班 = 
var x=LOOKUPVALUE('假日表'[日期],'假日表'[日期],[Date])
var y='日期表'[星期几] in {6,7}
var z =not LOOKUPVALUE('补休表'[日期],'补休表'[日期],[Date])
return
	if(x || y && z,0,1)

// 变量x:能在假日表[日期]列找到的肯定是法定假日，变量y和z:周六日的日期 and 不在补休表里的日期=休息日，if(法定假日 or 周末 and not 补休)

// 与或非优先级：not > and > or ; DAX中 and写成&& or写成||

// 我不习惯使用AND(条件1，条件2)， OR(条件1，条件2)

节日名称 = LOOKUPVALUE('假日表'[名称],'假日表'[日期],[Date])

// 当 日期表[Date] = 假日表[日期] 时，将 假日表[名称] V过来

上班序号 = if('日期表'[是否上班]=1,calculate(sum('日期表'[是否上班]),filter(ALL('日期表'),[Date]<=EARLIER('日期表'[Date]))))

// 日期表[是否上班] ：上班=1，休息=0，上了多少天班？ sum('日期表'[是否上班])

// 因为日期表Date不重复值

//《DAX神功》第1卷第14回 EARLIER函数每次筛选小于等于我当前行日期的表，用这张表筛选 sum('日期表'[是否上班]) 就是上班序号，也可以叫工作日的第几天

【6】如果想计算两个日期之间的天数

例如：2022/1/1~2022/1/5 工作日天数

// 度量值
工作日天数 = CALCULATE(SUM('日期表'[是否上班]),DATESBETWEEN('日期表'[Date],DATE(2022,1,1),DATE(2022,1,5)))

返回结果：

当然你也可以写成下面的公式，使用切片器 日期表[Date] 列控制这个度量值

工作日间隔天数=SUM('日期表'[是否上班])

【7】计算节假日销售

总销售 = SUM('销售表'[销量]) // 度量值

【8】指定日期的上一个与下一个工作日

假设我指定2022/1/16星期日，它的上一个工作日是小于2022/1/16的这张表中 and 是否上班=1 的最大日期

反之，指定日期2022/1/1，它的下一个工作日，是大于这个日期这张表中 and 是否上班=1 的最小日期。

// 度量值写法的原理，详见《DAX神功》第1卷第14回

// 度量值1
上个工作日 = 
    var x=SELECTEDVALUE('日期表'[Date])
return
    calculate(max('日期表'[Date]),filter(ALL('日期表'),'日期表'[是否上班]=1 && '日期表'[Date]<x))

// 度量值2
下个工作日 = 
    var y=SELECTEDVALUE('日期表'[Date])
return
    calculate(min('日期表'[Date]),filter(ALL('日期表'),'日期表'[是否上班]=1 && '日期表'[Date]>y))

返回结果：

【9】N个工作日之后

例如，你去办理身份证，会被告知7个工作日来取。指定日期N个工作日的计算方法

常识：7个工作日一般是不包含当天的，包含也没事就是将结果加1减1的事

假设你2022年1月5日来办理的身份证，我通知你7个工作日后来取，最快你要2022/1/14日来

计算方法：2022/1/5之后的7个工作日，就是2022/1/5的上班序号，（如果当日是休息日，不可能休息日谁办公呀，但是没关系）就找上一个上班序号+7。（如果是向前就-7）

// 你放心如果是1月1日上面没有数据了，他上班序号默认是0

7个工作日后 = 
    var x=SELECTEDVALUE('日期表'[Date])
    var d=calculate(max('日期表'[上班序号]),filter(ALL('日期表'),'日期表'[Date]<=x))
return
	calculate(max('日期表'[Date]),filter(ALL('日期表'),'日期表'[上班序号]=d+7))

你也可以选择使用参数表做可视化调整，可以直接在建模菜单下新建参数，但是有些版本可能没有这个按钮。我不习惯也想不起来用。

防止哪天微软一高兴又没了，直接告诉大家公式方法：《DAX神功》第2卷第6回

// 新建表：从0到30步长为1，方便做连续数字，或指定间隔数字
参数 = GENERATESERIES(0, 30, 1)

根据需求选择你要的表：《DAX神功》第1卷第3回

// 新建表： 可以是文字，数字等任何你指定的内容