自定义函数-日期函数

前言

这些自定义的日期函数添加了一些DAX语言所没有的日期函数，比如日期表创建函数、本周至今函数等，让日期的处理更加方便。

使用方式

复制以下DAX查询代码，粘贴到PowerBI的查询视图中运行，即可导入日期函数库。

关于每个函数的具体语法与介绍，请参考左边菜单栏的具体函数页面。

DEFINE
/// 根据指定的起始年份与结束年份，生成一张能覆盖所有日期的标准日期表；StartDayOfWeek参数用于指定每周的第一天，1:周日,2:周一
FUNCTION XF.Calendar.DateTable = (startYear:int64,endYear:int64,startDayOfWeek:anyref) =>
    ADDCOLUMNS(
        CALENDAR(DATE(startYear,1,1),DATE(endYear,12,31)),
        "Year Number",YEAR([Date]),
        "Year",FORMAT([Date],"yyyy"),
        "Month Number",MONTH([Date]),
        "Month",FORMAT([Date],"mmmm"),
        "Quarter Number",ROUNDUP(MONTH([Date])/3,0),
        "Quarter","Q"&ROUNDUP(MONTH([Date])/3,0),
        "WeekNum",WEEKNUM([Date],startDayOfWeek),
        "WeekDay Number",WEEKDAY([Date],startDayOfWeek),
        "WeekDay",
            VAR WeekDayNum = WEEKDAY([Date],startDayOfWeek)
            RETURN
            SWITCH(
                startDayOfWeek,
                1,
                    SWITCH(WeekDayNum,1,"周日",2,"周一",3,"周二",4,"周三" ,5,"周四",6,"周五" ,7,"周六"), 
                2,
                    SWITCH(WeekDayNum,1,"周一",2,"周二",3,"周三",4,"周四" ,5,"周五",6,"周六" ,7,"周日")
            ),
        "YearMonth Number",YEAR([Date])*100+MONTH([Date]),
        "YearMonth",FORMAT([Date],"yy-mmmm"),
        "YearQuarter Number",YEAR([Date])*10+ROUNDUP(MONTH([Date])/3,0),
        "YearQuarter",YEAR([Date])&"Q"&ROUNDUP(MONTH([Date])/3,0)
    )

/// 自动检测模型的日期字段，生成一张能覆盖所有日期的标准日期表；StartDayOfWeek参数用于指定每周的第一天，1:周日,2:周一
FUNCTION XF.Calendar.DateTableAuto = (startDayOfWeek:anyref) =>
    ADDCOLUMNS(
        CALENDARAUTO(),
        "Year Number",YEAR([Date]),
        "Year",FORMAT([Date],"yyyy"),
        "Month Number",MONTH([Date]),
        "Month",FORMAT([Date],"mmmm"),
        "Quarter Number",ROUNDUP(MONTH([Date])/3,0),
        "Quarter","Q"&ROUNDUP(MONTH([Date])/3,0),
        "WeekNum",WEEKNUM([Date],startDayOfWeek),
        "WeekDay Number",WEEKDAY([Date],startDayOfWeek),
        "WeekDay",
            VAR WeekDayNum = WEEKDAY([Date],startDayOfWeek)
            RETURN
            SWITCH(
                startDayOfWeek,
                1,
                    SWITCH(WeekDayNum,1,"周日",2,"周一",3,"周二",4,"周三" ,5,"周四",6,"周五" ,7,"周六"), 
                2,
                    SWITCH(WeekDayNum,1,"周一",2,"周二",3,"周三",4,"周四" ,5,"周五",6,"周六" ,7,"周日")
            ),
        "YearMonth Number",YEAR([Date])*100+MONTH([Date]),
        "YearMonth",FORMAT([Date],"yy-mmmm"),
        "YearQuarter Number",YEAR([Date])*10+ROUNDUP(MONTH([Date])/3,0),
        "YearQuarter",YEAR([Date])&"Q"&ROUNDUP(MONTH([Date])/3,0)
    )

/// 在指定的日期字段的当前可见数据中，以最大的日期为基准，返回本周至今的所有日期；StartDayOfWeek参数用于指定每周的第一天，1:周日,2:周一
FUNCTION XF.Calendar.DatesWTD = (dateCol:anyref,startDayOfWeek:anyref) =>
    DATESINPERIOD(dateCol,MAX(dateCol),-WEEKDAY(MAX(dateCol),startDayOfWeek),DAY)

/// 计算两个日期的间隔，以：xx天xx时xx分xx秒 的格式显示
FUNCTION XF.Calendar.DateDiffFormat = (startDate:datetime,endDate:datetime) =>
    VAR Diff = endDate-startDate
    VAR Times = FORMAT(Diff+TIME(0,0,0),"hh时nn分ss秒")
    VAR Days = INT(Diff)
    RETURN
    IF(Days<>0,Days&"天"&Times,Times)

/// 返回日期所处周的最后一天，StartDayOfWeek参数用于指定每周的第一天，1:周日,2:周一
FUNCTION XF.Calendar.EndOfWeeks = (datetime:datetime,startDayOfWeek:anyref) => datetime+7-WEEKDAY(datetime,startDayOfWeek)

/// 返回日期所处月份的最后一天
FUNCTION XF.Calendar.EndOfMonths = (datetime:datetime) => EOMONTH(datetime,0)

/// 返回日期所处季度的最后一天
FUNCTION XF.Calendar.EndOfQuarters = (datetime:datetime) => EOMONTH(DATE(YEAR(datetime),QUARTER(datetime)*3,1),0)

/// 返回日期所处年份的最后一天
FUNCTION XF.Calendar.EndOfYears = (datetime:datetime) => DATE(YEAR(datetime),12,31)

/// 检查值是否为日期
FUNCTION XF.Calendar.IsDate = (val:anyval) => ISdatetime(val)

/// 返回中国时区的日期，可解决发布到Service后的时区问题
FUNCTION XF.Calendar.LocalDate = () => DATEVALUE(UTCNOW()+TIME(8,0,0))

/// 返回中国时区的日期时间，可解决发布到Service后的时区问题
FUNCTION XF.Calendar.LocalDatetime = () => UTCNOW()+TIME(8,0,0)

/// 返回日期所处周的第一天，StartDayOfWeek参数用于指定每周的第一天，1:周日,2:周一
FUNCTION XF.Calendar.StartOfWeeks = (datetime:datetime,startDayOfWeek:anyref) => datetime-WEEKDAY(datetime,startDayOfWeek)+1

/// 返回日期所处月份的第一天
FUNCTION XF.Calendar.StartOfMonths = (datetime:datetime) => EOMONTH(datetime,-1)+1

/// 返回日期所处季度的第一天
FUNCTION XF.Calendar.StartOfQuarters = (datetime:datetime) => DATE(YEAR(datetime),QUARTER(datetime)*3-2,1)

/// 返回日期所处年份的第一天
FUNCTION XF.Calendar.StartOfYears = (datetime:datetime) => DATE(YEAR(datetime),12,1)

/// 将日期时间转换成时间戳
FUNCTION XF.Calendar.Timestamp = (datetime:datetime) =>
    VAR BenchmarkTime = DATE(1970,1,1)
    RETURN
    (datetime-BenchmarkTime)*24*60*60

/// 将时间戳转换成日期时间
FUNCTION XF.Calendar.TimestampToDatetime = (timestamp:int64) =>
    VAR BenchmarkTime = DATE(1970,1,1)
    RETURN
    BenchmarkTime+timestamp/24/60/60

其他

如果有任何问题或想法，欢迎在评论区提交你的需求与改进建议，一起完善该函数库！