Power BI - 计算累计一（WTD，YTD，MTD，QTD）

参考教程：

https://www.jianshu.com/p/723dc3988fea
https://zhuanlan.zhihu.com/p/88528732

一，累计开业至今

1历史至今 累计收入 = 
CALCULATE(
SUM('订单表'[销售额]),
FILTER(
ALL('日期表'),
'日期表'[日期]<=MAX('日期表'[日期])))  //用MAX函数获取当前上下文的日期;然后返回小于等于当前日期的所有销售额的合计

2历史至今 累计收入 优化 = 
IF(
MIN('日期表'[日期])<=[订单最大日期],
CALCULATE(
SUM('订单表'[销售额]),
FILTER(
ALL('日期表'),
'日期表'[日期]<=MAX('日期表'[日期]))))    //大于最后一个订单日期的无须计算，因为结果并不会发生变化

3历史至今 平均订单销售 = 
IF(
MIN('日期表'[日期])<=[订单最大日期],
CALCULATE(
AVERAGE('订单表'[销售额]),
FILTER(
ALL('日期表'),
'日期表'[日期]<=MAX('日期表'[日期]))))

二，本期至今

#年度至今(YTD)
[YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD('Date'[Date] ) )

以上表达式等价于：
[YTD Sales]:=TOTALYTD([Sales Amount],'Date'[Date])

#季度至今(QTD)
[QTD Sales] := CALCULATE ( [Sales Amount], DATESQTD('Date'[Date] ) )

以上表达式等价于：
[QTD Sales]:=TOTALQTD([Sales Amount],'Date'[Date])

#月度至今(MTD)
[MTD Sales] := CALCULATE ( [Sales Amount], DATESMTD('Date'[Date] ) )

以上表达式等价于：
[MTD Sales]:=TOTALMTD([Sales Amount],'Date'[Date])

#本周至今(WTD)
[WTD] = 
CALCULATE(
    [业务指标],
    FILTER(
        ALL('日期'),
        '日期'[Yearweek] = SELECTEDVALUE('日期'[yearweek]) 
        && '日期'[Date] <= SELECTEDVALUE('日期'[date]) ))

三，上期同期

#上年同期PY
PY= CALCULATE([SalesAmount],DATEADD('Date'[Date],-1,Year))

    以上表达式等价于#1：
    [PY YTD Sales] := CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( DATESYTD ( 'Date'[Date] ) ) )

    等价于#2：
    [PY YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) )

     等价于#3：
    [YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD('Date'[Date] ) )
　　[PY YTD Sales] := CALCULATE ( [YTD Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

    等价于#4：
    [PY Sales] := CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )  
　　[PY YTD Sales] := CALCULATE ( [PY Sales], DATESYTD ( 'Date'[Date] ) )

#上季同期PQ
PQ= CALCULATE([SalesAmount],DATEADD('Date'[Date],-1,Quarter))

#上月同期PM
PM=CALCULATE([SalesAmount],DATEADD('Date'[Date],-1,Month))

#上周同期PW（上周同日，七日前）
PW= CALCULATE([SalesAmount],DATEADD('Date'[Date],-7,day))

#计算任意期间的上一期 (by day)
1基础度量值：
本期收入 = SUM('订单表'[销售额])
2上期收入：
VAR DateStart = MIN('Date'[Day_Date])   //当前期间的开始日期
VAR DateEnd = MAX('Date'[Day_Date])    //当前期间的结束日期
VAR DateRangeLength = DATEDIFF(DateStart,DateEnd,DAY)    //当前期间的天数
VAR PreDateEnd = DateStart -1  //上期的结束日期
VAR PreDateStart = PreDateEnd - DateRangeLength   //上期的开始日期
VAR PreDareRange = DATESBETWEEN('Date'[Day_Date],PreDateStart,PreDateEnd)    //上个期间范围
RETURN
    CALCULATE([本期收入],PreDareRange)

#计算任意期间的上一期（by month）> 当x轴是 Y-Q-M 上下钻取，y轴是环比时可以用下面的公式算 环比的上一期

内部_上月 =

VAR DateStart = MIN('D_Date'[DATE]) //当前期间的开始日期

VAR DateEnd = MAX('D_Date'[DATE]) //当前期间的结束日期

VAR DateRangeLength = DATEDIFF(DateStart,DateEnd,MONTH) //当前期间的月份数

VAR PreDateEnd = EDATE(DateStart, -1) //上期的结束日期

VAR PreDateStart = EDATE(PreDateEnd,-DateRangeLength) //上期的开始日期

VAR PreDareRange = DATESBETWEEN('D_Date'[DATE],PreDateStart,PreDateEnd) //上个期间范围

VAR t1 = MAXX(FILTER(ALL('销售表'),'销售表'[sls_dt]),'销售表'[sls_dt]) //最大销售日期

Return

IF(DateStart <=t1,

CALCULATE(

[销售额],

PreDareRange

))

四，上期全部

#上年全部PYT(Previous Year Total)
PYT= CALCULATE([SalesAmount],PARALLELPERIOD('Date'[Date],-1,Year))

#上季全部（Previous Year Total）
PQT= CALCULATE([SalesAmount],PARALLELPERIOD('Date'[Date],-1,Quarter))

#上月全部（Previous Year Total）
PYT= CALCULATE([SalesAmount],PARALLELPERIOD('Date'[Date],-1,Month))

#上周全部（Previous Week Total）
PWT = 
IF(
    SELECTEDVALUE('DATE'[WEEKOFYEAR]) =1
    ||
    CALCULATE(
        COUNTROWS('DATE'),
        FILTER(ALL('DATE'),'DATE'[YEARWEEK] = SELECTEDVALUE('DATE'[YEARWEEK])-1)
    )<7,
    BLANK(),
    CALCULATE(
        [SALESAMOUNT],
        FILTER(ALL('DATE'),'DATE'[YEARWEEK] = SELECTEDVALUE('DATE'[YEARWEEK])-1))

五，去年

#去年同期
#计算去年同期利润(精确至天)
利润.新.YTD.PY.ByClaculateColumn=
CALCULATE( [利润.新], 
     CALCULATETABLE( SAMEPERIODLASTYEAR ( '日期表'[日期] ), '日期表'[大于最后订单日期] = TRUE ))

#计算去年的年度至今利润（精确至月）
利润.新.PY.ByMeasure=
VAR LastOrderDate =CALCULATE ( MAX ( '订单表.新'[订单日期] ), ALL ( '订单表.新' ) )
VAR FirstDayInSelection =MIN ( '日期表'[日期] )
VAR ShowData = ( FirstDayInSelection <= LastOrderDate )
VAR Result =IF ( ShowData, CALCULATE ( [利润.新], SAMEPERIODLASTYEAR ( '日期表'[日期] ) ) )
RETURN Result

等价于：
利润.新.PY.ByMeasure.Correct=
VAR LastOrderDate =CALCULATE ( MAX ( '订单表.新'[订单日期] ), ALL ( '订单表.新' ) )
VAR CurrentDates =FILTER ( VALUES ( '日期表'[日期] ), '日期表'[日期] <= LastOrderDate )
VAR Result =CALCULATE ( [利润.新], SAMEPERIODLASTYEAR ( CurrentDates ) )
RETURN Result

#去年本季LY_QTD
CALCULATE(
        [本季至今(QTD)],
        SAMEPERIODLASTYEAR('D_DATE'[DAY_DATE]))

#去年本月LY_MTD
CALCULATE(
        [本月至今(MTD)],
        SAMEPERIODLASTYEAR('D_DATE'[DAY_DATE]))

#去年本周LY_WTD
CALCULATE(
        [本周至今(WTD)],
        SAMEPERIODLASTYEAR('D_DATE'[DAY_DATE]))

注：以上只是个公式汇总，未全部验证过。大概是这么个套路，应用时需要根据具体的数据和切片器做调整。

实战 1 - 计算历史月份和将来月份的汇总值
当前月份为202104，将历史月份（202101 - 202104）标为YTD，剩余月份（202105 - 202112 ）标为YTG；对应去年历史（202001 - 202004）标为LY_YTD，去年剩余（202005 - 202012）标为LY_YTG

注：取当前日期 = NOW() , 取去年同期 = EDATE(NOW(),-12)

##DAX公式
Tag =
VAR current_yearmonth = VALUE(FORMAT(NOW(),"YYYYMM"))
VAR lastyearmonth = VALUE(FORMAT(EDATE(NOW(),-12),"YYYYMM"))
VAR current_year = VALUE(YEAR(NOW()))
VAR lastyear = VALUE(YEAR(EDATE(NOW(),-12)))
Return
Switch(TRUE(),
'YearMonth'[Year] = current_year && 'YearMonth'[YearMontNo] < current_yearmonth,"YTD",
'YearMonth'[Year] = current_year && 'YearMonth'[YearMontNo] >= current_yearmonth,"YTG",
'YearMonth'[Year] = lastyear && 'YearMonth'[YearMontNo] < lastyearmonth,"LY_YTD",
'YearMonth'[Year] = lastyear && 'YearMonth'[YearMontNo] >= lastyearmonth,"LY_YTG"
)

实战 2 - 用Fiscal Month做维度时，计算值不对
解决：参考财年计算，加上财年结束日期 - https://view.inews.qq.com/a/20210119A04WZG00

实战3 - 计算LY_MTD没值，日期表与事实表用Date_ID 关联
解决：将Date 表标记为日期表，标记为日期表可以自动移除来自日期表其他列的筛选器，这种特性非常重要。
参考标记日期标的作用：https://www.powerbigeek.com/mark-as-date-table/

posted @ 2021-02-26 14:25 未必是小小阅读(9866) 评论(0) 编辑收藏举报

刷新页面返回顶部

未必是小小

Power BI - 计算累计一（WTD，YTD，MTD，QTD）

公告