日期表兼容农历之自定义函数 fxCalendar

153-模型-日期表兼容农历之自定义函数 fxCalendar

一、背景

我们在做 Power BI 或者 Power Pivot 项目时候,几乎每个模型都会遇到的一个维度表,这个维度表就是日期表(Calendar),日期表对 DAX 建模有举足轻重的作用。

在各种复杂的业务场景中,对于时间来或者日期来说,大家都是平等的,其实对业务的划分也就大致相同。常规的年、季度、月、周、日等。在中国还有一个很重的特征就是农历的日期,在一些业务场景下,农历的日期也是单独做分析的;这个时候,我们常规的日期表就捉襟见肘了。

今天我们就发布一个兼容农历日期的 Power Query 自定义函数:fxCalendar ,先来看一下自定义函数。

附件下载https://jiaopengzi.com/2635.html

image-20220524153010584

二、日期表解析

日期表字段说明

字段名称 结果 说明
C01_Dates 2022/1/1 日期
C02_Day 1 日期天
C03_WeekDay 5 周几数字,周一为:0,周日为:6
C04_WeekCNS1 周几中文简写1字
C05_WeekCNS3 星期六 周几中文简写3字
C06_WeekENS1 S 周几英文简写1字
C07_WeekENS3 Sat 周几英文简写3字
C08_WeekEN Saturday 周几英文
C09_WeekIndex 6366 周索引:1900-1-1,星期一,第1周
C10_WeekNumber 1 周一开始的一年中第几周
C11_Week W01 周的W简写
C12_YearWeek Y22W01 年周组合
C13_Month 1 月份数字
C14_MonthM M01 月份M简写
C15_MonthCN 一月 月份中文
C16_MonthENS3 Jan 月份英文简写3字
C17_MonthEN January 月份英文
C18_YearMonthM Y22M01 年月M简写
C19_YearMonthUS Jan-22 年月英文简写
C20_YearMonth 202201 年月数字组合
C21_Quarter 1 季度数字
C22_QuarterQ Q1 季度Q简写
C23_YearQuarterQ Y22Q1 年季度Q简写
C24_YearQuarter 202201 年季度数字组合
C25_HalfOfYearCN 上半年 中文半年度
C26_HalfOfYearEN H1 半年度H简写
C27_YearHalf Y22H1 年度季度简写组合
C28_Year 2022 年度数字
C29_FY00 FY22 年度FY简写
C30_FY FY FY:全年的意思,与月度等同维度辅助字段
C31_StartOfWeek 2021/12/27 C01_Dates对应周开始的日期
C32_StartOfMonth 2022/1/1 C01_Dates对应月开始的日期
C33_StartOfQuarter 2022/1/1 C01_Dates对应季度开始的日期
C34_StartOfHalfYear 2022/1/1 C01_Dates对应半年度开始的日期
C35_StartOfYear 2022/1/1 C01_Dates对应年度开始的日期
C36_EndOfWeek 2022/1/2 C01_Dates对应周结束的日期
C37_EndOfMonth 2022/1/31 C01_Dates对应月结束的日期
C38_EndOfQuarter 2022/3/31 C01_Dates对应季度结束的日期
C38_EndOfHalfYear 2022/6/30 C01_Dates对应半年度结束的日期
C40_EndOfYear 2022/12/31 C01_Dates对应年度结束的日期
C41_Holiday 元旦节 假期名称
C42_Animal 年份属相
C43_LunarDateCN 廿九 C01_Dates对应的中文农历日期
C44_LunarMonthCN 十一 C01_Dates对应的中文农历月份
C45_LunarDate 29 C01_Dates对应的农历日期数字
C46_LunarMonth 11 C01_Dates对应的农历月份数字
C47_LunarYear 2021 C01_Dates对应的农历年数字
C48_Status 假期 日期状态包含:假期、周末、补班、工作日。

日期表实际效果图示

image-20220524155740853

image-20220524155950811

image-20220524160132929

image-20220524160319761

image-20220524160537682

三、日期表自定义函数解析

1、常规日期表参考之前的文章:https://jiaopengzi.com/566.html

2、本次日期表主要加入了农历的元素,农历的知识博大精深,我们在这里只是把常规日历与农历日期对应起来。

3、农历和常规日历的对应非常复杂,我们通过 Power Query 在百度的日历 API 接口获取的农历相关信息,再与常规日历拼接起来的。

image-20220524162029576

4、Power Query 中自定义函数的 M 代码。

M 代码查看地址:https://jiaopengzi.com/2635.html

5、复制上述 M 语句,打开 Power Query 新建空白查询,在高级编辑器中粘贴上述 M 语句,最后把查询的名称更改为 fxCalendar 即可。

四、总结

1、日期表在 DAX 建模中很重要。

2、一张好日期表可以在后续的 DAX 度量值设计以及可视化视觉的应用中起到很大的作用,比如这里的FY,还有年、半年度、季度、月度、周的开始日期以及结束日期等。

3、本次文章的重点是更新了常规日期表如何去和农历结合,这个对特定的业务场景非常有用。

4、同时我们后续还会依赖这个日期表做一系列的关于日期时间维度的内容。

by 焦棚子