之前介绍过用M语言创建自增长日历,这个日历属于单纯的自增长日历,适用于与对日期时间没有特殊差别的统计报表使用。但是对于HR妹子,她们要统计加班记录,往往就需要知道是否有员工在周末或休息时间也去上班了,所以,从而HR妹子的角度来看,一个标注有休息日和假期信息的日历才是好日历。要制作这种日历,也比较简单。
先制作一个包含美国节假日的日历,这次创建基础日历表使用简单的DAX公式进行。
US Calendar = CALENDAR(DATE(2018,1,1),DATE(2018,12,31))
之后可以提取出Month以及Day的相关信息。此处为了以后创建Report方便,对于Day的提取使用了FORMAT公式,即按照Monday,Tuesday等更易读的形式提取日期,而不是直接提取数字。同时,使用了WEEKDAY函数,将Monday,Tuesday等转换成了数字1-7,以便后续计算。
Month = MONTH('US Calendar'[Date])
Day = FORMAT('US Calendar'[Date],"DDDD")
Weekday = WEEKDAY('US Calendar'[Date],2)
基础数据准备完毕之后我们需要另外一组数据,即US的放假时间表。用万能的Bing查找一份,发现http://www.calendarpedia.com/holidays/federal-holidays-2018.html 上记载的US Federal Holidays数据比较干净。决定就用它了。
点击Power BI中的获取数据(Get Date)- Web,输入包含US Federal Holidays的网址,然后选择确定(OK)。
Power BI会自动加载网页中的数据,并将其“可能是”表单的数据提取出来。选择包含我们需要的holiday数据的表单然后进行编辑。
Power BI自动分析出的表单中存在一点脏数据,根据我们的需求,可以将收尾两行去掉,并删除多余的column 3,然后整理出一份干净的holiday信息。
得到US Federal Holidays后我们需要跟之前的US Calendar表创建关联关系,这样才能将holiday中的数据情况反应在US Calendar表上。
搞定两张表之间的关系后就可以在US Calendar表中创建一个列来标记当前日期是否是工作日。公式如下:
Isworkingday = IF('US Calendar'[Weekday]>0&&'US Calendar'[Weekday]<6&&COUNTX(RELATEDTABLE('US Federal Holidays'),'US Calendar'[Date])<1,"Yes","No")
If中有三个判断,前两个比较好理解,即根据Weekday中的值,把周一到周五这些日子标记成working day,后一个调用了COUNTX+RELATEDTABLE函数来查看哪些日期是公共假期。至于是怎么查看的呢?先说一下COUNTX的基本用法:
COUNTX(<table>,<expression>)
COUNTX的中英文解释都比较绕,它实际的意义是对一个表单数行数,怎么数呢?是根据expression定义的条件进行数数。第二个参数expression部分多数填写的都是一个列名,也就是数数的对象,并且该对象只能是:数字,时间和字符串。此外COUNTX是一个聚合函数,如果在计算列中使用,针对每一行的计算结果,实际上是整个表单符合条件的结果。
COUNTX(RELATEDTABLE('US Federal Holidays'),1)
乍看一下COUNTX可实现的功能似乎与我们要根据US Federal Holidays表去标记US Calendar中Date列没什么关系,但其实此处的核心其实RELATEDTABLE函数。这个函数可以从我们之前创建的US Calendar与US Federal Holidays两张表的1对1关系中通过筛选生成一整新的表,该表只包含US Calendar和US Federal Holidays两张表中相同的数据。
RELATEDTABLE函数的用法非常简单:
RELATEDTABLE(<tableName>)
由于此处我们新建了一个计算列,所以RELATEDTABLE函数的使用过程是:依次从US Calendar中拿出创建relationship使用的Date列下的每一行值,然后去relationship另一端US Federal Holidays表中的date列下查看是否有匹配的数据,如果有,就会用这个date生成一个新表。之后外围的COUNTX顺势就会计算当前刚刚生成的表有几行,然后返回行数。(COUNTX的expression填写了一个常量1,目的就是不以任何条件的数当前表的行数。)。当US Calendar中Date列下的某行值在US Federal Holidays中不存在时,就会返回BLANK。而当COUNTX计算的表是BLANK时,它的返回结果也会是BLANK。
通过这种方式,我们实际上是在US Calendar中创建出来一个列,如果当前date是Federal Holiday,当前列的结果就是1(US Calendar表中没有重复日期,所以只有唯一匹配,因此COUNTX的结果肯定是1。);如果不是,列值就是BLANK。
在得到了哪些日期是公共假期之后,我们就可以顺利的标记出working day了。
美国的日历制作完毕,同理还可以制作一个中国区的日历。不过与美国的放假规则不太一样,国内的假期有串休的概念,即某些周六周日是正常的工作日。因此针对这种情况,中国区的日历要比美国的稍微复杂一些。我们需要将国内的放假情况做一个拆分,只做两张表,一张是单纯记录周一到周五属于法定节假日的情况,另外一张则是记录周六和周日是法定上班时间的情况。
搞定两张表之后可以与CN Calendar创建关联关系然后使用相同原理来标记工作日。
Isworkingday = IF(('CN Calendar'[Weekday]>0&&'CN Calendar'[Weekday]<6&&COUNTX(RELATEDTABLE('CN Holiday'),1)<1)||COUNTX(RELATEDTABLE('CN Weekend Work'),1)=1,"Yes","No")