Power BI 时间智能函数

一、基础知识

1，环比：MOM (Month over Month ) = （当前月金额- 上月金额）/上月金额*100

2，同比：YOY (Year over Year ) = （当前月金额- 上一年金额）/上一年金额*100

3，年累计：YTD(Year to day)  年初到今日的销售额之和。

4，季累计：QTD(Quarter to day)  季初到今日的销售额之和。

5，月累计：MTD(Month to day)  月初到今日的销售额之和。

6，移动年度总计MAT ：过去12月销售之和。

7，移动年度平均MAA moving Annual Average: 过去12个月销售平均

二、如何创建时间表

date = 
  var   TEMP1 = calendar (date (2022,1,1), date (2025,12,30))
return 
ADDCOLUMNS( TEMP1,
"DATE_CHAR",format([Date],"YYYYMMDD"),
"max_date" , date(2999,12,30),--自定义一个最大日期
 "month_num", DATEDIFF( [date]-1, eomonth([date],0),day), //月底减去每天得到日期
 "min_date" , datevalue("1900/01/01"),
"day", day([date]),//返回一月中日期1-31数字
"edate", edate([date],1),// 返回一个月之后的日期
"eomonth0" ,eomonth([date],1),--平移指定月份然后取月底日期
"Hour", hour([date]),-- 取小时
"now_date",now(),-- 返回一个日期和时间
"TIME" ,time(9,30,48),-- 返回一个时间
"today",TODAY(),--返回0点时间
"utctoday",utcTODAY(),--返回0点时间
//"endofmonth" ,ENDOFMONTH('DATE'[Date].[Date]) ,
// "endofquarter" ,ENDOFQUARTER('DATE'[Date].[Date]) ,
// "endofyear" ,ENDOFyear('DATE'[Date].[Date]) ,
// "dateadd1" ,dateadd('calendar'.[date].[date],1,day),-- 粒度day,month,quarter,year
"year_code" ,year([date]),
"year_desc" ,year([date])&"年",
"half_code" ,  if(month([date])<=6,"H1","H2"),
"half_code_desc" ,  if(month([date])<=6,"上半年","下半年"),
"quarter",roundup(month([date])/3,0),
"quarter_desc",roundup(month([date])/3,0)&"Q",
"year_quarter",year([date])& roundup(month([date])/3,0),
"year_quarter_desc",year([date])&"-"& roundup(month([date])/3,0)&"Q",
"month",month([date]),
"month_code",format([Date],"MM"),
"month_code1",month([date])&"月",
"year_month",year([date])& month([date]),
"year_month_code",year([date])& format( [date] ,"mm"),
"year_month_desc",year([date])&"年"& format( [date] ,"mm")&"月",
"weekday",weekday([date],1),--n=1 默认 周日是1，
"weekday1",weekday([date],2),
"weekday_desc",switch(weekday([date],1),1,"星期日",2,"星期一",3,"星期二",4,"星期三",5,"星期四",6,"星期五",7,"星期六"),--n=1 默认 周日是1，
"week1",weeknum([date],1),--周日开始
"week2",weeknum([date],2),--周一开始
"week11",weeknum([date],11),--周一开始
"week12",weeknum([date],12),--周一开始
"week13",weeknum([date],13),--周一开始
"week14",weeknum([date],14),--周一开始
"week15",weeknum([date],15),--周一开始
"week16",weeknum([date],16),--周一开始
"week17",weeknum([date],17),--周一开始
 "week21",weeknum([date],21)--ISO跨年周 
)        

三、各种应用场景

3.1  同比，环比，季度比，

MOM = var last_month = CALCULATE([sum_qty],DATEADD('date'[Date],-1,MONTH))  return     IF( ISBLANK([sum_qty] ) || ISBLANK(last_month), BLANK(),iferror(divide( ([sum_qty]-last_month),last_month) ,BLANK())   )

MON_VAR = var last_month = CALCULATE([sum_qty],DATEADD('date'[Date],-1,month)) return [sum_qty]- last_month

QOQ = var last_quarter = CALCULATE([sum_qty],DATEADD('date'[Date],-1,QUARTER))  return     IF( ISBLANK([sum_qty] ) || ISBLANK(last_quarter), BLANK(),
iferror(divide( ([sum_qty]-last_quarter),last_quarter) ,BLANK())   )

YOY = var last_year = CALCULATE([sum_qty],DATEADD('date'[Date],-1,YEAR))  return     IF( ISBLANK([sum_qty] ) || ISBLANK(last_year), BLANK(),
iferror(divide( ([sum_qty]-last_year),last_year) ,BLANK())   )

但是新需求要一个动态的环比，可以通过创建参数来实现。等价代码如下

DMOM = var Param= SELECTEDVALUE(P_int[P_int],-1)  var last_month = CALCULATE([sum_qty],DATEADD('date'[Date],Param,MONTH))  return     IF( ISBLANK([sum_qty] ) || ISBLANK(last_month), BLANK(),
iferror(divide( ([sum_qty]-last_month),last_month) ,BLANK())   )

3.2 累计值

MTD = TOTALMTD([sum_qty],'date'[Date])
QTD = TOTALQTD([sum_qty],'date'[Date])
YTD = TOTALYTD([sum_qty],'date'[Date])

中国式报表更喜欢周累计：WTD

WTD = calculate( [sum_qty], FILTER(ALL('date'), 'date'[week1]=SELECTEDVALUE('date'[week1]) && 'date'[Date]<=max('date'[Date])))

3.3 年度移动平均。

--1，最近一年销售
MAT = CALCULATE([sum_qty],DATESINPERIOD('date'[Date],max('date'[Date]),-1,YEAR))
--2，最近一年销售
MAT1 = CALCULATE([sum_qty],DATESBETWEEN('date'[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('date'[Date]))),LASTDATE('date'[Date])))
--3、动态销售
DMAT1 = CALCULATE([sum_qty],DATESINPERIOD('date'[Date],max('date'[Date]),'P_int'[P_int 值],MONTH))
MAA = CALCULATE( DIVIDE([sum_qty],DISTINCTCOUNT('date'[year_month_code])), DATESINPERIOD('date'[Date],MAX('date'[Date]),-1,YEAR))  --开始月份和结束月份平均值不对
--4、动态2动态销售
DMAT2 = CALCULATE([sum_qty],DATESBETWEEN('date'[Date],NEXTDAY(DATEADD(LASTDATE('date'[Date]),'P_int'[P_int 值],MONTH)),LASTDATE('date'[Date])))


5，--动态一年平均
MAA = 
 var firstdateinsales = CALCULATETABLE(FIRSTDATE('Fact'[DATE_ID]),ALL('Fact')) -- calculatetable(exp,filter)  firstdate(date) 获取事实表最开始日期 为了计算最开始 平均月份的个数
 var lastdateinsales = CALCULATETABLE(LASTDATE('Fact'[DATE_ID]),ALL('Fact'))  --  获取事实表最开始日期  为了计算最后几个月平均月份的个数
 var firstdateindate = TREATAS(firstdateinsales,'date'[Date]) -- 将fact日期数据沿袭改为日期 必须的。
 var lastdateindate = TREATAS(lastdateinsales,'date'[Date])-- 将fact日期数据沿袭改为日期 必须的。
 return 
 CALCULATE( DIVIDE([sum_qty],DISTINCTCOUNT(   'date'[year_month] )), DATESINPERIOD('date'[Date],MAX('date'[Date]),-1,YEAR), 'date'[Date]>=firstdateindate ,'date'[Date]<=lastdateindate)

--6、动态平均DMAA = 
 var firstdateinsales = CALCULATETABLE(FIRSTDATE('Fact'[DATE_ID]),ALL('Fact')) -- calculatetable(exp,filter)  firstdate(date) 获取事实表最开始日期 为了计算最开始 平均月份的个数
 var lastdateinsales = CALCULATETABLE(LASTDATE('Fact'[DATE_ID]),ALL('Fact'))  --  获取事实表最开始日期  为了计算最后几个月平均月份的个数
 var firstdateindate = TREATAS(firstdateinsales,'date'[Date]) -- 将fact日期数据沿袭改为日期 必须的。
 var lastdateindate = TREATAS(lastdateinsales,'date'[Date])-- 将fact日期数据沿袭改为日期 必须的。
 return 
 CALCULATE( DIVIDE([sum_qty],DISTINCTCOUNT(   'date'[year_month] )), DATESINPERIOD('date'[Date],MAX('date'[Date]),'P_int'[P_int 值],MONTH), 'date'[Date]>=firstdateindate ,'date'[Date]<=lastdateindate)

其实这里MAA 需要优化，一个是需要优化动态值，还有如果之前月份是空，也是除以12个月。

优化后

3.4 总结

从 202301 MON_VAR  :   1-blank() =    1
从 202301 MOM  :    1/ blank() =   blank()
从 202407 MON_VAR  :  blank() - 1 =  -1
BLANK () + BLANK () = BLANK ()
10 * BLANK () = BLANK ()
BLANK () / 3 = BLANK ()
BLANK () / BLANK () = BLANK ()
BLANK () || BLANK () = FALSE
BLANK () && BLANK () = FALSE
BLANK () = BLANK () = TRUE
BLANK () - 10 = -10
18 + BLANK () = 18
4 / BLANK () = 无穷大   从 202301 MOM  :    1/ blank() =    blank() 待确认
0 / BLANK () = NaN
FALSE || BLANK () = FALSE
FALSE && BLANK () = FALSE
TRUE || BLANK () = TRUE
TRUE && BLANK () = FALSE



对于时间序列函数因为本案例最小是月，所以偏移量都是粒度大约月的数字。
如果对于天粒度的进行使用时间智能函当偏移粒度不为日时候需要考虑大小月和大小年。
视觉对象/单元格元素可以对每一列进行调整颜色/背景。