110_Power Pivot特殊结算日期及财年日期

焦棚子 2020-03-0119:01:45评论1,832字数 1579阅读5分15秒阅读模式

焦棚子的文章目录文章源自焦棚子-https://jiaopengzi.com/236.html

请在文末下载附件

 文章源自焦棚子-https://jiaopengzi.com/236.html

1、背景

前几天看到群里有朋友在搞特殊结算日期,主要不是按照正常日期里的整月,按照比如:上月21号至本月20号作为结算周期,由于这样的结算日期,无法使用pp里面的时间智能函数。文章源自焦棚子-https://jiaopengzi.com/236.html

pp内置了很多时间智能函数,但是都无法满足需求,dax只要你创建上下文即可满足业务需求,今天就简单写个这样的度量值。文章源自焦棚子-https://jiaopengzi.com/236.html

2、数据源

calendar表文章源自焦棚子-https://jiaopengzi.com/236.html

2017/1/1-2018/12/31文章源自焦棚子-https://jiaopengzi.com/236.html

fact表文章源自焦棚子-https://jiaopengzi.com/236.html

2017/1/1-2018/12/31的随机value文章源自焦棚子-https://jiaopengzi.com/236.html

110_Power Pivot特殊结算日期及财年日期
数据源

关系建立文章源自焦棚子-https://jiaopengzi.com/236.html

110_Power Pivot特殊结算日期及财年日期
关系

3、上DAX

total文章源自焦棚子-https://jiaopengzi.com/236.html

total:=SUM('fact'[value])

ytd_1231文章源自焦棚子-https://jiaopengzi.com/236.html

按照每年12月31号为结算年末的ytd,注意观察datesytd第二参数:"12-31"文章源自焦棚子-https://jiaopengzi.com/236.html

ytd_1231:=CALCULATE('fact'[total],DATESYTD('calendar'[date],"12-31"))

ytd_0630文章源自焦棚子-https://jiaopengzi.com/236.html

按照每年6月30号为结算年末的ytd,注意观察datesytd第二参数:"06-30"文章源自焦棚子-https://jiaopengzi.com/236.html

ytd_0630:=CALCULATE('fact'[total],DATESYTD('calendar'[date],"06-30"))

mtd文章源自焦棚子-https://jiaopengzi.com/236.html

按照正常整月的mtd文章源自焦棚子-https://jiaopengzi.com/236.html

mtd:=CALCULATE('fact'[total],DATESMTD('calendar'[date]))

mtd2文章源自焦棚子-https://jiaopengzi.com/236.html

1、按照结算周期(上月21日-本月20日,其中D是参数可以更改为自己需求)的mtd,可以看月维度及天维度;文章源自焦棚子-https://jiaopengzi.com/236.html

2、考虑闰年2月29天因素文章源自焦棚子-https://jiaopengzi.com/236.html

3、由于本案例中只存在2017年以后数据,2017年1月mtd即是1月1日-1月20日之间文章源自焦棚子-https://jiaopengzi.com/236.html

mtd2:=
VAR D = 20//D设置为参数,D的范围应该在(1,28)
VAR MP =
    COUNTROWS ( PREVIOUSMONTH ( 'calendar'[date] ) )
VAR MSD =
    STARTOFMONTH ( 'calendar'[date] )
VAR N =
    SWITCH (
        TRUE (),
        MP = 31, D - 31,
        MP = 30, D - 30,
        MP = 29, D - 29,
        MP = 28, D - 28
    )
VAR SD =
    IF (
        ISFILTERED ( 'calendar'[date] ),
        IF (
            DAY ( MAX ( 'calendar'[date] ) ) <= D,
            DATEADD ( MSD, N, DAY ),
            DATEADD ( MSD, D, DAY )
        ),
        DATEADD ( MSD, N, DAY )
    )
VAR ED =
    IF (
        ISFILTERED ( 'calendar'[date] ),
        MAX ( 'calendar'[date] ),
        DATEADD ( MSD, D - 1, DAY )
    )
VAR T =
    DATESBETWEEN ( 'calendar'[date], SD, ED )
RETURN
    CALCULATE ( 'fact'[total], T )

结果图文章源自焦棚子-https://jiaopengzi.com/236.html

110_Power Pivot特殊结算日期及财年日期
结果图
110_Power Pivot特殊结算日期及财年日期
透视表

请关注文章源自焦棚子-https://jiaopengzi.com/236.html

1、mtd中2017年1月(1月1日-1月31日),即蓝色单元格;文章源自焦棚子-https://jiaopengzi.com/236.html

2、mtd2中2017年1月(1月1日-1月20日),即黄色单元格;文章源自焦棚子-https://jiaopengzi.com/236.html

3、mtd2中2017年2月(1月21日-2月20日),即橙色单元格(颜色如果不是橙色也暂且叫做橙色吧);文章源自焦棚子-https://jiaopengzi.com/236.html

4、2017年6月中ytd_1231、ytd_0630都是相等,即红色字体单元格;文章源自焦棚子-https://jiaopengzi.com/236.html

5、2017年7月中ytd_1231、ytd_0630不相等,即文章源自焦棚子-https://jiaopengzi.com/236.html

绿色单元格:datesytd第二参数中,ytd_1231按照12月31日为结算年末,文章源自焦棚子-https://jiaopengzi.com/236.html

红色单元格:datesytd第二参数中,ytd_0630按照6月30日为结算年末。文章源自焦棚子-https://jiaopengzi.com/236.html

4、总结

1、好像没啥总结的;文章源自焦棚子-https://jiaopengzi.com/236.html

2、datesytd注意第二参数的使用,主要是结算或者财年使用;文章源自焦棚子-https://jiaopengzi.com/236.html

3、特殊结算周期,根据业务特点创建DAX上下文即可;文章源自焦棚子-https://jiaopengzi.com/236.html

 文章源自焦棚子-https://jiaopengzi.com/236.html

by 焦棚子文章源自焦棚子-https://jiaopengzi.com/236.html

焦棚子的文章目录文章源自焦棚子-https://jiaopengzi.com/236.html

请点击【立即购买】或者【升级VIP】获得案例附件。文章源自焦棚子-https://jiaopengzi.com/236.html

隐藏内容需要支付:¥5
立即购买 升级VIP
文章源自焦棚子-https://jiaopengzi.com/236.html
焦棚子
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接!
  • 《110_Power Pivot特殊结算日期及财年日期》链接:https://jiaopengzi.com/236.html