Kai Site

简体中文

English

简体中文

English

外观

DAX 文本、财务与时间智能

13605 字约 45 分钟

2026-01-28

本页定位

这篇笔记整理 DAX 的文本函数、信息函数、财务函数和时间智能函数，适合构建经营分析中的日期和财务指标。

学习重点

  • 掌握 FORMAT、CONCATENATEX、SEARCH 等文本处理函数。
  • 理解 PV、FV、XIRR 等财务函数的应用场景。
  • 建立日期表和时间智能指标，如同比、环比、累计和滚动窗口。

文章目录

一、文本函数

1.1 FORMAT函数

参考《格式字符串详解》

FORMAT 函数主要用于将数值、日期时间等数据类型按照指定的格式转换为字符串。这对于在报表中以特定的格式显示数据非常有用，比如货币格式、日期格式、百分比格式等。其函数语法为：

FORMAT(<value>, <format_string>[, <locale_name>])
  • value：要格式化的值，可以是数值、日期时间、布尔值等。
  • format_string：指定的格式字符串，用于定义如何格式化值。
  • locale_name： 可选，表示区域设置的名称，详见视频
1.1.1 数字格式
基础格式符号说明示例
0占位符，强制显示数字（无数字时显示 0）0000 → 0123（输入 123）
#可选占位符，无数字时不显示###-## → 12-34（输入 1234）
.小数点分隔符0.00 → 123.45
,千位分隔符或缩放数值（如 ，结尾表示千单位）#,##0 → 1,234；0.0, → 1.2K（输入 1234）
%百分比格式（自动乘以 100）0% → 123%（输入 1.23）
$、€ 等货币符号$#,##0.00 → $1,234.56
条件格式（分号 ；分隔）分三段定义正数、负数、零的格式+#,##0.00;-#,##0.00;"空" → 正数带加号，负数带负号，零显示"空"
常用数字格式（以12345.67为例）格式说明返回值
“0”整数格式，无小数位12345
“0.0”保留一位小数12345.7
“0%”百分比格式，乘以100并添加%符号1234567%
“0.00%”百分比格式，保留两位小数1234567.00%
“0.00E+00”科学计数法格式1.23E+04
“#,###”千位分隔符格式12,345
“#,## 0.00”千位分隔符格式，保留两位小数1,234.56
“¥#,## 0.00”带人民币符号的千位分隔符格式，保留两位小数¥1,234.56
“General Number”无格式化，直接显示数字12345.67
“Currency”按照本地货币格式显示，示例为美国货币格式$12,345.67
“Fixed”小数点前至少一位，小数点后两位12345.67
“Standard”小数点前至少一位，小数点后两位，包含千位分隔符，示例为美国数字格式12,345.67
“Percent”以百分比形式显示，数值乘以 100，带有百分号1,234,567.00%
“Scientific”以科学计数法显示，保留两位小数1.23E+04
“Yes/No”如果数字为 0，则显示NO；否则，显示YES。
“True/False”如果数字为 0，则显示False；否则，显示True。
“On/Off”如果数字为 0，则显示 Off;否则，显示“On。
1.1.2 日期/时间格式
日期/时间格式说明输出
“General Date”显示当前区域的日期或时间2025/3/24 19:23:57
“Long Date”根据当前区域的长日期格式显示日期2025年3月24日
“Medium Date”根据当前区域的中日期格式显示日期25-03-24
“Short Date”根据当前区域的短日期格式显示日期2025/3/24
“Long Time”使用当前区域的长时间格式显示时间19:23:57
“Medium Time”使用当前区域的中时间格式显示时间07:23 下午
“Short Time”使用24小时格式显示时间19:23
日期/时间格式 ，以2018-1-1为例输出格式参数输出
D1M1
DD01MM01
DDDMonMMMJan
DDDDDMondayMMMMJanuary
AAA周一OOO1月
AAAA星期一OOOO一月
Q1YY18
YYYY2018YYYYMM201801
yyyy-mm-dd2018-01-01yyyy年m月d日2018年1月1日
dd/mm/yyyy欧洲日期格式，01/01-2018mm/dd/yyyy美国日期格式，01/01/2018
yyyy-mm-dd hh:nn2018-01-01 13:45m月d日hh时nn分 1月1日 13时45分

利用FORMAT函数，制作完整的日期表：
在这里插入图片描述

1.1.3 自定义格式

除了以上预设的格式化函数，你也可以自定义格式。在自定义数字格式字符串时，格式模板可以有 一到三个部分，每个部分之间用分号分隔：

  1. 只有一个部分：格式表达式应用于所有值，例如"$#,##0"。
  2. 有两个部分：第一部分应用于正数和零，第二部分应用于负数，例如"$#,##0;($#,##0)"。
  3. 有三个部分：第一部分应用于正数，第二部分应用于负数，第三部分应用于零，例如"$#,##0;($#,##0);Zero"
自定义格式描述示例输出
货币格式表示货币格式，千位分隔，两位小数FORMAT(1234.56, "$#,0.00")$1,234.56
日期格式表示完整的日期格式FORMAT(DATE(2023, 10, 11), "DDDD, MMMM DD, YYYY")Wednesday, October 11, 2023
时间格式表示12小时制的时间格式FORMAT(TIME(14, 30, 45), "HH:MM:SS AM/PM")02:30:45 PM
同比1 = FORMAT([同比], " 0% ; (0%) ")
同比2 = FORMAT([同比]," +0% ; -0% ; 0% ")
同比3 = FORMAT([同比],"0%↑ ; -0%↓ ; - ")
同比4 = FORMAT([同比]," 增长 ; 下降 ; - ")

在这里插入图片描述

你也可以在格式窗口中直接设置数据的显示格式，比如正数前面显示“+”。不过这里的设置只适合直接显示，用FORMAT函数的方式不仅能直接显示特定的格式，还可以嵌套在DAX中使用，使用场景更加丰富。

在这里插入图片描述

1.2 CONCATENATE与CONCATENATEX

CONCATENATE的用法很简单，就是连接两个字符串，类似连接符&，其语法为

CONCATENATE(<text1>, <text2>)

其迭代函数CONCATENATEX功能强大，很多场景都用得到，其作用是按表达式和分隔符将每一行的字符连接为一个文本字符串：

CONCATENATEX(<table>, <expression>[, <delimiter> [, <orderBy_expression> [, <order>]]...])
  • <table>：用于迭代每一行的表
  • <expression>：每一行字符串的表达式
  • <delimiter>：分割符，可选
  • <orderBy_expression>：排序表达式，可选
  • <order>：排序类型，默认为DESC （降序），可改为 ASC （升序）

由于度量值的结果都只能是一个值，当需要返回多个值时，就可以利用CONCATENATEX函数将多个值连接成一个字符串输出。

1.2.1 返回多个类别名称

对于多选的切片器，如果想获取切片器的选项，其结果就是一个列表，这时就可以用CONCATENATEX函数将列表合并成一个字符串来返回，比如：

产品切片器 多选=
CONCATENATEX(
   VALUES('产品表'[产品名称]),
   [产品名称],						// 字符串表达式，这里是是每一行的产品名称字符
   "、"								// 分隔符
)

在这里插入图片描述

1.2.2 返回多个类别的名称和数据，并排序

如果不仅想展示切片器所选的产品，还需要展示出该产品的利润，同时按利润进行排序，可以这样写：

产品利润 从高到底=
CONCATENATEX(
    VALUES('产品表'[产品名称]),
    [产品名称]&"："&[利润],
    UNICHAR(10),					// UNICHAR(10)表示换行符，让每一行单独展示
    [利润],							// 按利润大小进行排名
    DESC
)

在这里插入图片描述

1.3 使用SEARCH进行模糊查找

SEARCH函数的功能与Excel中的类似，返回一个文本在另一个文本中的起始位置：

SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
  • find_text,within_text：要查找的字符和被查找的文本；
  • start_num：可选，查找开始的位置，默认为1；
  • NotFoundValue：未找到时返回的值，可选（但强烈建议），通常为 0,-1,BLANK。不指定则返回错误。

查找文本位置这个原始功能使用场景不多，更常用的是结合FILTER函数进行模糊匹配，例如从下面这些长尾关键词的搜索数据中，找到包含“数据分析”的搜索量有多少。

数据分析搜索量 =
CALCULATE(
    SUM('数据表'[搜索量]),
    FILTER(
        '数据表',
        SEARCH("数据分析", [搜索长尾词], 1, 0)
    )
)

在这里插入图片描述

上图表格中的数据只截取了一部分，免得表格太长，所以最终计算出来的结果并不正确，只做展示用。

这里SEARCH函数搜索每一行的数据中，是否包含字符串"数据分析"，有则返回位置（整数，视为True），无则返回0（视为Fasle），所以SEARCH(“数据分析”, [搜索长尾词], 1, 0)可视为一个布尔筛选器，使得FILTER可以筛选出所需的数据。

如果想同时包含两个关键字的搜索量，还可以在第一个参数中使用通配符来完成（SEARCH函数支持通配符）：

上海数据分析搜索量 =
CALCULATE(
    SUM('数据表'[搜索量]),
    FILTER(
        '数据表',
        SEARCH("上海*数据分析", [搜索长尾词], 1, 0)
    )
)

如果有个关键词列表，想汇总每一行数据的搜索量：

关键词搜索量 =
VAR keywords = SELECTEDVALUE('关键词表'[关键词])
RETURN
CALCULATE(
    [搜索量],
    FILTER(
        '数据表',
        SEARCH(keywords, [搜索长尾词], 0)
    )
)

在这里插入图片描述

SEARCH函数与FIND函数的区别是：FIND不支持通配符，并且严格区分大小写；其他情况都可以使用SEARCH函数，SEARCH的应用范围更广。

二、信息函数

2.1 ISINSCOPE

ISINSCOPE。ISINSCOPE用于判断指定列是否是级别层次结构中的级别，如果是，返回True，其语法为：

ISINSCOPE(<columnName>)

比如在销售明细表中，计算产品销售额的总体占比及其在类比内部的分类占比：

总销售占比 = [销售总额]/CALCULATE([销售总额],ALL('销售明细'))
各品牌每年销售占比 = [销售总额]/CALCULATE([销售总额],ALLSELECTED('产品明细'[品牌]))

现在要实现的效果是：当处于品牌类别层级，显示类别在总体的占比；当打开品牌类别时，显示产品在类别的占比，类似于将1和2的效果合起来：

层级占比 = SWITCH(
    TRUE(),
    ISINSCOPE('产品明细'[产品名称]),[销售总额]/CALCULATE([销售总额],ALLSELECTED('产品明细')),
    ISINSCOPE('产品明细'[品牌]),[销售总额]/CALCULATE([销售总额],ALLSELECTED('产品明细'[品牌])),
    [销售总额]/CALCULATE([销售总额],ALLSELECTED('产品明细'))											//条件都不满足时的默认值
)

在这里插入图片描述

三、财务函数

参考《认识Power BI中的财务函数》

函数名描述参数
PV根据固定利率计算现金流的现值
FV根据固定利率计算现金流的终值
PDURATION返回投资达到指定值所需的期数rate（利率）, pv（现值）, fv（未来值）
RRI返回投资的每期收益率nper（期数）, pv（现值）, fv（未来值）
XIRR返回不一定具有周期性的现金流时间表的内含收益率table（表格）, values（现金流）, dates（日期）

3.1 PV与FV

PV 函数用于根据固定利率计算现金流的现值，其语法为： PV(<rate>, <nper>, <pmt>[, <fv>[, <type>]])

  • rate: 利率。
  • nper: 总期数。
  • pmt: 每期支付金额。
  • fv: 未来值（可选，默认为0）。
  • type: 支付时间类型，可选。0表示期末支付（默认），1表示期初支付。

假如每月还房贷5000元，年利率5%，20年还清，则现值（也就是初始贷款额）为：

// 其中0.05/12是将年利率折算为月利率，20*12是总还款月数
贷款额(20年月供5000年息5%) = PV( 0.05/12 , 20*12 , -5000)

FV与PV正好相反，它根据固定利率计算现金流的终值。如果你每月存5000，年利率5%，你想知道存够20年以后会变成多少钱，就可以用这个函数，其语法为： FV(<rate>, <nper>, <pmt>[, <pv>[, <type>]])

  • rate: 利率。
  • nper: 总期数。
  • pmt: 每期支付金额。
  • pv: 现值（可选，默认为0）。
  • type: 支付时间类型（可选，0或1，默认为0）。
月存五千20年后有多少钱 = FV(0.05/12,20*12,-5000)

在这里插入图片描述

3.2 PDURATION 、RRI 与XIRR

  1. PDURATION函数用于计算投资达到指定值所需的期数，其语法为：

    PDURATION(<rate>, <pv>, <fv>)
    • rate : 每期的利率。通常为年利率除以期数（如月利率）。
    • ​pv : 现值，即初始投资金额或贷款金额。
    • ​fv : 未来值，即目标金额。

比如年收益率10%，投入5万元，多少年后可以翻倍（变成10万），就可以用这个函数：

收益率10%几年可翻倍 =PDURATION(0.10,50000,100000)

  1. 通过RRI函数，已知现值、终值和期数，就可以计算出每期的收益率，其语法为：

    RRI(<nper>, <pv>, <fv>)
    • ​nper : 总期数。
    • ​pv : 现值，即初始投资金额或贷款金额。
    • ​fv : 未来值，即目标金额。

假设投资100元，5年后获得200元，也就是5年投资收益翻一倍，其年化收益率是多少呢？

5年翻倍的年化收益率 = RRI( 5 , 100 , 200 )

在这里插入图片描述

  1. XIRR：RRI计算每期收益率，是在非常规律的周期性现金流的基础上的，这种计算起来比较简单。但是实际情况中很多投资现金流是不规律的，此时可以使用XIRR 函数计算其内含报酬率（IRR），其语法为：
XIRR(<table>, <values>, <dates>, [, <guess>[, <alternateResult>]])
  • <table>：包含现金流数据的表，该表必须包含两列：现金流金额（Values）及其对应的日期（Dates）。
  • <values>：表中表示现金流金额的列，这些金额可以是正数（流入）或负数（流出）。
  • <dates>：表中现金流发生日期的列，必须与 Values 列中的金额一一对应。
内含收益率 = XIRR( '投资明细表' , [现金流] , [日期] )

在这里插入图片描述

四、时间智能函数

工作中常常会遇到对时间数据的对比分析，比如使用时间智能函数计算上年同期数据：

[上年同期]= CALCULATE([数量],
                    SAMEPERIODLASTYEAR('日期表'[日期]))

时间智能函数和普通函数的区别：

  • 日期函数：直接依赖当前行上下文，一般作为新建列使用，比如YEAR函数，提取日期列的年度
  • 时间智能函数：可以重置上下文，一般在新建度量值时使用，可以快速移动到指定的时间区间

4.1 日期表

使用时间智能函数时若出现一些莫名其妙的错误，或者返回的数据难以解释，很大可能是你使用的时间参数有问题，所以首先应该建立一个合格的日期表，它应该具备以下的特征：

  • 起止日期涵盖事实表的所有日期
  • 日期是连续且不重复的，不含有空值
  • 必须标记为日期表

以下面格式的日期表为例，介绍其制作方式：

在这里插入图片描述

4.1.1 使用源数据作为日期表

如果源数据库或数据仓库中已经存在日期表，可以直接使用，这种表通常已经包含了公司需要的日期信息（如节假日、会计年度等）。使用源数据表的优点是可以与 Power BI 之外的其他工具共享。

4.1.2 使用Power Query制作日期表

List.Dates 是 Power Query M 语言中的一个函数，用于生成一个指定范围内的日期列表，其语法为：

List.Dates(start as date, count as number, step as duration) as list
  • start：日期类型，表示列表的起始日期。
  • count：数字类型，表示列表中要生成的日期数量。
  • step：持续时间类型，表示每个日期之间的间隔

例如，以下函数用于生成2011 年 5 月 31 日至未来10年的日期数据：

= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))

此时还只是一个日期列表，而不是一个日期表。选择转换 > 为表，将其转为表格，然后将其类型转为日期类型：

在这里插入图片描述
完成日期类型选择后，可以添加年、月、星期和天的列。 转到添加列，选择日期下的下拉菜单，然后选择年，如下图所示。

在这里插入图片描述

要生成开头那种格式的日期表，可直接调用以下函数：

(optional 请输入开始年份 as number,

optional 请输入结束年份 as number)=>
let
x = 请输入开始年份,
y = if 请输入结束年份 = null

then 请输入开始年份 else 请输入结束年份,
begin_date = if x = null

then #date(Date.Year(DateTime.LocalNow()),1,1)
else #date(x,1,1),
end_date = if y = null then #date(Date.Year(DateTime.LocalNow()),12,31)

else #date(y,12,31),
list = {1..Number.From(end_date)-Number.From(begin_date)+1},
dates = List.Transform( list , (item)=> Date.AddDays(begin_date,item-1) ),
table = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(dates,
Splitter.SplitByNothing(), null, null, ExtraValues.Error),{ {"Column1", "日期"} }),{ {"日期", type date} }),
year_id = Table.AddColumn(table,"年度", each Date.Year([日期]), type number),
quarter_name = Table.AddColumn(year_id, "季度", each "Q"&Text.From(Date.QuarterOfYear([日期]))),
month_id = Table.AddColumn(quarter_name, "月份", each Text.PadStart(Text.From(Date.Month([日期])),2,"0")),
data_id=Table.AddColumn(month_id,"日", each Date.Day([日期]), type number),
year_quarter_id = Table.AddColumn(data_id, "年度季度", each Text.From([年度])&[季度]),
year_month_id = Table.AddColumn(year_quarter_id, "年度月份", each Date.Year([日期])*100+ Date.Month([日期]), type number),
day_in_week = Table.AddColumn(year_month_id, "星期几", each Number.Mod(Date.DayOfWeek([日期])+6,7)+1, type number)
in
day_in_week

在这里插入图片描述

4.1.3 使用 DAX 函数制作日期表

  • CALENDAR(<start_date>, <end_date>)：根据指定的开始日期和结束日期生成连续的日期范围，可以使用返回日期/时间值的任何 DAX 表达式，例如Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))。

  • CALENDARAUTO([fiscal_year_end_month])：CALENDARAUTO 会自动扫描数据模型中的所有日期列（但不包括计算列），并根据这些日期列的范围生成模型中最早日期到最晚日期所在的财政年度的所有日期。fiscal_year_end_month用于指定财政年结束月份，默认为12。

    假设模型中MinDate 和 MaxDate 分别为 2010 年 7 月 1 日和 2011 年 6 月 30 日

    • CALENDARAUTO() 生成 2010 年 1 月 1 日至 2011 年 12 月 31 的日期表
    • CALENDARAUTO(3) 生成 2010 年 4 月 1 日至 2012 年 3月 31 的日期表

制作完成后，还可以添加其他列（如年份、月份、周数、星期几等）来丰富日期表：

Year = YEAR(Dates[Date])
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")

在这里插入图片描述

要制作开头那种格式的日期表，可以使用ADDCOLUMNS与CALENDAR函数：

日期表 =ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2018,12,31)),
	"年度", YEAR ( [Date] ),
	"季度", "Q" & FORMAT ( [Date], "Q" ),
	"月份", FORMAT ( [Date], "MM" ),
	"日",FORMAT ( [Date], "DD" ),
	"年度季度", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ),
	"年度月份", FORMAT ( [Date], "YYYY/MM" ),
	"星期几", WEEKDAY ( [Date],2 )
)

或者是使用 GENERATE与CALENDARAUTO函数，制作一个动态的日期表（CALENDARAUTO会自动自动检测模型中其他表中所有日期来生成日期表，使用VAR函数可以节省内存，提高运行速度）。

日期表=
  GENERATE (
    CALENDARAUTO(),
    VAR currentDay = [Date]
    VAR year =  YEAR ( currentDay )
    VAR quarter =   "Q" & FORMAT ( currentDay, "Q" )
    VAR month =  FORMAT ( currentDay, "MM" )
    VAR day = DAY( currentDay ) 
    VAR weekid =  WEEKDAY ( currentDay,2)
  RETURN   ROW (
   "年度", year ,
   "季度",quarter,
   "月份", month,
   "日", day,
   "年度季度", year&quarter, 
   "年度月份", year&month,
   "星期几", weekid
    )
  )

以下函数可生成更详尽的日期表，可按需求仅删改：

日期表 = 
VAR YearStart = 2019    //起始年度
VAR YearEnd = 2021      //结束年度

VAR WeekNumberType = 2
    // WEEKNUM第二个参数类型，用于确定一周的起始日以及周数的计算方式，默认为1
    // 1，一周从星期日开始 
    // 2，一周从星期一开始 

VAR WeekDayType = 2       
    // WEEKDAY第二个参数类型，控制每周的开始时间，返回周几的编号，默认为1
    // 1，一周从星期日 (1) 开始，到星期六 (7) 结束，编号 1 到 7
    // 2，一周从星期一 (1) 开始，到星期日 (7) 结束，编号 1 到 7
    // 3，一周从星期一 (0) 开始，到星期日 (6) 结束，编号 0 到 6
-----------------------------------------------------------------

RETURN

GENERATE (
    CALENDAR( DATE( YearStart , 1 , 1 ) , DATE( YearEnd , 12 , 31 ) ),
    VAR Year = YEAR ( [Date] )
    VAR Month = MONTH ( [Date] )
    VAR Quarter = QUARTER( [Date] )
    VAR Day = DAY( [Date] )
    VAR YearMonth = Year * 100 + Month
    VAR Weekday = WEEKDAY( [Date] , WeekDayType ) 			
    VAR WeekOfYear = WEEKNUM( [Date] , WeekNumberType ) 	
    RETURN ROW (
        "年" , Year ,
        "季" , Quarter ,
        "月" , Month ,
        "日" , Day ,
        "年度名称" , "Y" & Year ,
        "季度名称" , "Q" & Quarter ,
        "年度季度", Year & "Q" & Quarter ,
        "年季编号" , ( Year - YearStart )*4 + Quarter,
        "月份名称", FORMAT ( [Date], "OOOO" ) ,
        "英文月份", FORMAT ( [Date], "MMM" ) ,
        "年度月份" , YearMonth ,
        "年月编号" , ( Year - YearStart )*12 + Month,
        "年度第几日" , INT( [Date] - DATE( Year , 1 , 1 ) + 1 ),      
        "星期编号" , Weekday ,
        "星期名称" , FORMAT( [Date] , "AAAA" ) ,
        "星期英文" , FORMAT( [Date] , "DDD" ) ,  
        "年度第几周" , WeekOfYear ,
        "周编号", "W" & RIGHT( 0 & WeekOfYear , 2 ) ,
        "年周" , Year & "W" & RIGHT( 0 & WeekOfYear , 2 ) ,
        "日期编码" , Year * 10000 + Month * 100 + Day
    )
)
  • 便于月份排序：日期表中对月份、季度都添加有数字列，可以更方便的进行按列排序，比如对中文的月份字段进行按列排序
  • 添加年月序号和年季序号字段：在不适合使用时间智能函数的分析场合，利用这个连续的序号分析十分方便
  • 提供了中英文的月份和星期字段、以及周相关的字段，满足更多的场景需求

在这里插入图片描述

4.1.4 标记为日期表

生成日期表后，首先要在报表视图或表格视图中，把它标记为日期表。标记的好处是，它会强制检查日期表的日期列是否为日期格式、是否为连续的、不重复的，如果不是，则无法进行标记。这样的强制要求保证了后面使用时间智能函数时，不会出现这方面的错误。

在这里插入图片描述

自动日期/时间功能 是 Power BI 的一个特性，它会自动为日期字段生成层次结构（如年、月、日）。当你将一个表标记为日期表时，Power BI 会从该表的 Date 字段中删除自动生成的层次结构，但你可以手动为其添加层次结构。

日期表应当包含分析需要的维度列，而不是不一定要按照网上搜罗的方法生成同样的粒度列。比如要按财年来分析，就要按照财年的起止日期来添加财年列；如果要进行最近12个月的滚动分析，就添加一个月份编号。优秀的日期表可以简化DAX的嵌套，建立简单的度量值就可以轻松聚合所需要的数据。

4.1.5 应用自动日期/时间选项

“自动日期/时间”是 Power BI Desktop 中的一个数据加载选项，为用户提供了便捷的日期维度处理方式。当启用此功能时，Power BI 会自动为导入表中的日期列生成隐藏的日期表。在应用场景比较简单时，可以直接使用层级结构进行筛选、分组、向下钻取、使用时间智能，而无需手动创建日期表。

在这里插入图片描述

你可以在全局或当前文件中配置此选项。启用此功能的条件是，表存储模式为“导入”，列数据类型为“日期”或“日期/时间”，列不是模型关系中的“多”方。开启后，Power BI Desktop 会为每个日期列创建一个隐藏的自动日期/时间表。这些表使用 CALENDAR生成，并包含六个计算列：Day,MonthNo,Month,QuarterNo,Quarter,Year。自动日期/时间表定义了层次结构，为视觉对象提供年份、季度、月份和日期级别的向下钻取路径。当 Power BI 刷新模型时，自动日期/时间表也会被刷新，以确保模型始终包含日期列值的完整日期范围。

自动日期/时间表是隐藏的，无法直接在字段窗格或模型视图中看到，报表作者可以通过展开带有日历图标的字段来访问层次结构，并在视觉对象中使用这些层次结构。

在这里插入图片描述

使用 Power BI Desktop 编写的公式可以按常规方式引用日期列，然后使用.引用自动日期/时间表中的列：

在这里插入图片描述

4.2 函数简介

  1. 返回时间区间的函数
函数名描述
DATESMTD, DATESQTD, DATESYTD本月至今 、本季度至今 、 本年至今的日期范围
FIRSTDATE, LASTDATE返回第一个日期 、返回最后一个日期
PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR返回上一日、上一个月、上一个季度、上一个年度
NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR返回下一日、下一个月、下一个季度、下一个年度
ENDOFMONTH , ENDOFQUARTER ,ENDOFYEAR返回所属月度的最后一天、季度的最后一天、年度的最后一天
STARTOFMONTH, STARTOFQUARTER, STARTOFYEAR返回所属月度的第一天、季度的第一天、年度的第一天
SAMEPERIODLASTYEAR上年同期
DATEADD移动一定间隔后的时间段
DATESBETWEEN返回从起始日到结束日的时间段
DATESINPERIOD返回从指定日期移动一定间隔的时间段
PARALLELPERIOD返回移动指定间隔的完整粒度的时间段

以上函数中，前20个函数只需要一个日期参数，一般结合CALCULATE使用，返回对应的时间期间。DATEADD到DATESINPERIOD这四个函数的参数复杂一点，一般用于相对时间区间的控制：

函数名函数语法参数说明
DATEADDDATEADD(<dates>,<number>,<interval>)1. 日期列
2. 整数（正数表示向未来移动，负数表示向过去移动）
3. 移动的粒度（可以是 year,quarter,month,day）
DATEBETWEENDATESBETWEEN(<Dates>, <StartDate>, <EndDate>)1. 日期列
2. 起始日期
3. 结束日期（包含结束日期当天）
PARALLELPERIODPARALLELPERIOD(<dates>,<number>,<interval>)1. 日期列
2. 整数（正数表示向未来移动，负数表示向过去移动）
3. 移动的粒度（可以是 year,quarter,month,day）
DATESINPERIODDATESINPERIOD(<dates>, <start_date>, <intervals>, <interval>)1. 日期列
2. 开始日期
3. 整数（正数表示向未来移动，负数表示向过去移动）
4. 移动的粒度（可以是 year,quarter,month,day）
// 计算上年同期销量，等同于 SAMEPERIODLASTYEAR
CALCULATE([数量], DATEADD('日期表'[日期], -1, YEAR))

// 计算年初至今，相当于 TOTALYTD
CALCULATE([数量], DATESBETWEEN('日期表'[日期], STARTOFYEAR('日期表'[日期]), LASTDATE('日期表'[日期])))

// 计算上年全年的销量。
CALCULATE([数量], PARALLELPERIOD('日期表'[日期], -1, YEAR))

// 返回过去7天的日期
DATESINPERIOD('日期表'[日期], MIN('日期表'[日期]), -7, DAY))
// 计算每月最后7天的销售额
CALCULATE([数量], DATESINPERIOD('日期表'[日期], ENDOFMONTH('日期表'[日期]), -5, DAY))
  1. 执行运算的函数
类型函数注释
累计计算TOTALMTD, TOTALQTD, TOTALYTD返回月初至今、季初至今、年初至今的累计值
余额计算CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR返回该月最后一天、该季度最后一天、该年度最后一天的数据
OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR返回上月最后一天、上季度最后一天、上年最后一天的数据

这9个函数更加智能，不仅可以重置上下文，甚至直接对重置后的下上文执行运算，把CALCULATE都省掉了，比如求年初至今的销量，使用DATESYTD可以写作：

= CALCULATE([数量],DATESYTD(日期表[日期]))

TOTALYTD函数语法为：

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

此度量值可简写为：

= TOTALYTD（[数量],日期表[日期])

DATESYTD,PREVIOUSYEAR,NEXTYEAR,ENDOFYEAR,STARTOFYEAR,TOTALYTD,CLOSINGBALANCEYEAR,OPENINGBALANCEYEAR，这8个年度参数中都还有一个可选参数<year_end_date>，表示年度结束日期，默认为12月31日。

在进行财年的分析时，很多国外的公司的财年都不是自然年度，比如苹果的财年结束日期9月30日，2017财年就是从2016年10月1日到2017年9月30日，计算2018财年的年初至今的销量：

=CALCULATE([数量],DATESYPD(日期表[日期],"9-30"））

等同于：

TOTALYTD=（[数量],日期表[日期],"9-30"）

4.3 各种时间度量

参考《各种时间指标的度量值》

假设数据模型为一张订单表和一张对应的日期表，并已经建立了基础度量值：

收入 = SUM('订单'[销售额])
4.3.1 本期至今（XTD）

本期至今即从本期的第一天到当前日期的累计。

// 月初至今(MTD,Month To Date),两种写法都可以
MTD = CALCULATE([收入],DATESMTD('日期表'[日期]))
MTD = TOTALMTD([收入],'日期表'[日期])

// 季初至今(QTD,Quarter To Date)
QTD = CALCULATE([收入],DATESQTD('日期表'[日期]))
QTD = TOTALQTD([收入],'日期表'[日期])

// 年初至今(YTD，Year To Date)
QTD = CALCULATE([收入],DATESYTD('日期表'[日期]))
QTD = TOTALYTD([收入],'日期表'[日期])
4.3.2 上一期的本期至今（PXTD，计算环比）

上期的MTD，简称为PMTD，比如上个月的MTD。

// 上月的月初至今(PMTD,Previous Month to Date)
PMTD = CALCULATE([MTD],DATEADD('日期表'[日期],-1,MONTH))
PMTD = TOTALMTD([收入],DATEADD('日期表'[日期],-1,MONTH))

// 上季的季初至今(PQTD,Previous Quarter to Date)
PQTD = CALCULATE([QTD],DATEADD('日期表'[日期],-1,QUARTER))
PQTD = TOTALQTD([收入],DATEADD('日期表'[日期],-1,QUARTER))

// 上年的年初至今(PYTD,Previous Year to Date)
PYTD = CALCULATE([YTD],DATEADD('日期表'[日期],-1,YEAR))
PYTD = TOTALYTD([收入],DATEADD('日期表'[日期],-1,YEAR))
PYTD = CALCULATE([YTD],SAMEPERIODLASTYEAR('日期表'[日期]))
PYTD = TOTALYTD([收入],SAMEPERIODLASTYEAR('日期表'[日期]))

有了以上数据，就可以计算环比增长：

// 与上个月的差异(MOM,Month Over Month)
MOM = IF([PMTD]<>BLANK(),[MTD]-[PMTD])   

// 月环比(MOM %,Month Over Month Percentage)
MOM % = DIVIDE([MOM],[PMTD])

// 与上个季度的差异(QOQ,Quarter Over Querter)
QOQ = IF([PQTD]<>BLANK(),[QTD]-[PQTD])

// 季度环比(QOQ %,Quarter Over Querter Percentage)
QOQ % = DIVIDE([QOQ],[PQTD])
4.3.3 上一年的本期至今（PY XTD，计算同比）

上年的本年至今，其实通过PXTD的思路也能计算出来，只是因为上年的本期至今太常用了，就单独再介绍一下。

// 上年的月初至今(PY MTD,Previous Year Month to Date)
PY MTD = CALCULATE([MTD],SAMEPERIODLASTYEAR('日期表'[日期]))

// 上年的季初至今(PY QTD   Previous Quarter Month to Date)
PY QTD = CALCULATE([QTD],SAMEPERIODLASTYEAR('日期表'[日期]))

// 上年的年初至今(略)

有了以上数据，就可以计算同比增长（YOY %,Year Over Year Percentage)

// 本月累计与上年同期的差异
YOY MTD  = IF([PY MTD]<>BLANK(),[MTD]-[PY MTD])

// 本月同比
YOY MTD % = DIVIDE([YOY MTD],[PY MTD])

// 本季累计与上年同期的差异
YOY QTD = IF([PY QTD]<>BLANK(),[QTD]-[PY QTD])

// 本季同比
YOY QTD % = DIVIDE([YOY QTD],[PY QTD])

// 本年累计与上年同期的差异
YOY YTD = IF([PY YTD]<>BLANK(),[YTD]-[PY YTD])

// 年同比增长
YOY YTD % = DIVIDE([YOY YTD],[PY YTD])

4.4 基础示例

4.4.1 动态对比任意时间段数据

除了同比和环比，业务分析中还会遇到的一种场景是，选择任意区间的两组日期，展示其数据对比情况。假设有以下数据模型，包含订单表以及对应的日期表、产品表。想要达到的效果是，通过两个日期切片器，来选择两个时间段，报告中分别展示这两个时间段的产品销售额。

在这里插入图片描述
在这里插入图片描述

  1. 建立’比较日期表’
    因为需要两个互不影响的日期切片器，来选择不同的时间段，所以两个日期表是必须的，直接复制原日期表就可以了，即比较日期表 = '日期表'。
    在这里插入图片描述
  2. 建立非活动关系
    如果两个日期表直接建立物理关系，依然会相互筛选，无法生成两个独立的时间段，所以这里的做法是建立非活动的虚线关系：
    在这里插入图片描述
  3. 建立度量值。其中，通过清除原日期表的筛选，并激活非活动关系，这样’比较日期表’的日期，就可以通过原日期表来筛选订单表，返回比较期间的收入。
当期收入 = SUM( '订单表'[销售额] )
比较期间收入 =
VAR compareperiod= 										// 获取比较日期表的所选期间
    CALCULATETABLE(										// 第一个参数通常是表表达式，后接一系列筛选条件
        VALUES('日期表'[日期]),							// 获取唯一值列表
        REMOVEFILTERS('日期表'), 						// 清除原日期表的筛选，也可以使用ALL函数
        USERELATIONSHIP('日期表'[日期],'比较日期表'[日期]) 	// 激活非活动关系
    )
RETURN
CALCULATE([当期收入], compareperiod)
4.4.2 隐藏未来日期

参考《利用DAX修正同期数据》

在 Power BI 中，如果事实表数据是随时间动态更新的，而日期表包含所有年份的完整日期，那么在报表中使用日期智能函数（如 YTD）时，可能会显示未来日期或未发生交易的日期数据。比如，一份订单事实表中记录的利润数据截至2018年6月15日，将年度至今的利润度量值放入报表，可以看到未来日期的利润数据也显示出来了：

利润.新.YTD=CALCULATE( [利润.新] , DATESYTD( '日期表'[日期] ) )

在这里插入图片描述
要隐藏未来日期数据，可以通过计算列或者度量值来进行日期筛选。

4.4.2.1 使用计算列

  1. 创建辅助列：辅助列用于判断日期表的每个日期是否大于事实表的最大订单日期：

    大于最后订单日期= '日期表'[日期] <=MAX( '订单表.新'[订单日期] )

在这里插入图片描述
2. 结合 CALCULATETABLE 筛选日期：

利润.新.YTD.ByClaculateColumn= 
CALCULATE (
   [利润.新],
   CALCULATETABLE (
        DATESYTD ( '日期表'[日期] ), 
        '日期表'[大于最后订单日期] = TRUE )
)

推而广之，该方法可作为处理此类问题的标准套路:：

CALCULATE(
    [度量值],
    CALCULATETABLE (
        <日期智能函数> (日期),
        '日期表'[大于最后订单日期] =TRUE
    )
)

但如果作为筛选判断依据的特定日期是动态变化的，或者无法使用新建列的情况下（比如通过DirectQuery直连模式访问数据），就必须使用度量值的方式了。

4.4.2.2 使用度量值

通过 IF 条件判断日期是否在最大交易日期范围内：

利润.新.YTD.ByMeasure = 
    VAR LastOrderDate = CALCULATE(MAX('订单表.新'[订单日期]), ALL('订单表.新'))
    VAR FirstDayInSelection = MIN('日期表'[日期])
    VAR ShowData = (FirstDayInSelection <= LastOrderDate)
    VAR Result = IF(ShowData, CALCULATE([利润.新], DATESYTD('日期表'[日期])))
    RETURN Result

在这里插入图片描述

4.4.2.3 度量值 vs 计算列：统计粒度的区别

而使用同样的套路计算去年的年度至今利润，会发现最后一个月，两种方式统计的结果不一样：

利润.新.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

在这里插入图片描述

将最后一个月的数据向下钻取仔细核对，会发现计算列的方式统计的数据会精确到每一天，即事实表中最后交易日为2018/6/15，则统计的去年同期数据截止到2017/6/15；而度量值方法则依旧统计的是整个6月的数据。如果要统计到日粒度，可使用FILTER以最后订单日为界限按日筛选日期表（VAR CurrentDates）

利润.新.PY.ByMeasure.Correct=
	VAR LastOrderDate = CALCULATE ( MAX ( '订单表.新'[订单日期] ), ALL ( '订单表.新' ) )
	VAR  CurrentDates = FILTER ( VALUES ( '日期表'[日期] ), '日期表'[日期] <= LastOrderDate )
	VAR Result = CALCULATE ( [利润.新], SAMEPERIODLASTYEAR ( CurrentDates ) )
RETURN
    Result
  • 计算列方法：适合大多数场景，通过辅助列控制日期筛选，逻辑清晰且易于实现。
  • 度量值方法：适合更复杂的动态场景，但需要根据分析需求调整数据颗粒度（如按天、按月汇总）。
4.4.3 计算最佳纪录

业务分析中，除了常见的同比、环比等时间维度的对比外，与历史最佳记录的比较也是一种重要的分析方法，比如找出每个日期之前的最高收入数据。

前期单日收入记录 = MAXX(
    FILTER(
        SUMMARIZE(
            ALL('日期表'), '日期表'[日期], "每日收入", [收入]
        ),
        '日期表'[日期] <= MIN('日期表'[日期])
    ),
    [每日收入]
)

在这里插入图片描述

4.4.4 自动显示最新数据

业务中一种常见情况是让报表自动显示本月、上月、上年同期数据，以及对应的同比和环比。一种简单的做法是日期表中加上年月序号。假设日期表是从2018年1月1日开始，则：

// 2018-01 序号为1,2019-01序号为13，以此类推
年月序号 = ( YEAR( [日期] ) - 2018 ) * 12 + MONTH( [日期] )

因为没有外部日期上下文，所以利用TODAY函数来找出今日所对应的年月编号，就可以计算出本月数据：

本月 =
VAR cur_yearmonth =
    CALCULATE(SELECTEDVALUE('日期表'[年月序号]),'日期表'[日期] = TODAY())
RETURN
    CALCULATE ([收入],'日期表'[年月序号] = cur_yearmonth)

上月数据为cur_yearmonth - 1，上年同期为cur_yearmonth - 12，这样同比环比也算出来了：

在这里插入图片描述
以上是整月数据计算，如果想计算本月至今的数据，以及相对应的上月、去年同期对比，该怎么做呢？首先需要构造一个从本月1号到今天的日期列表：

收入 MTD =
CALCULATE(
    [收入],
    FILTER(
        ALL('日期表'),
        '日期表'[日期]>=EOMONTH(TODAY(),-1)+1
        &&'日期表'[日期]<=TODAY()
    )
)

EOMONTH是一个日期函数，返回移动指定月份后的当月的最后一天日期，比如EOMONTH("2021-3-15",-1)将返回2021年2月的最后一天日期:2021-2-28。

EOMONTH可以返回移动后的月份的最后一天的日期，所以通过这个函数构造出EOMONTH(TODAY(),-1)+1，将返回本月第一天的日期，利用这个逻辑，顺利计算出本月至今的数据。

同样的方式，计算上个月的本月至今：

收入 MTD PM =
CALCULATE(
    [收入],
    FILTER(
        ALL('日期表'),
        '日期表'[日期]>=EOMONTH(TODAY(),-2)+1
        &&'日期表'[日期]<=EDATE(TODAY(),-1)
    )
)

EDATE也是一个日期函数，返回移动指定月份后的对应日期，比如EDATE("2021-3-15",1)将返回2021-4-15,如果移动后的日期在对应的月份中不存在，晚于当月最后一天，则自动返回该月的最后一天，比如EDATE("2021-3-30",-1)，2月是没有30号的，则自动返回为最后一天2021-2-28。

在这里插入图片描述

计算上月同期数据时候，还有个小问题是，如果本月的天数比上个月少，比如2021年2月份，2月28号是最后一天，本月至今就是2月整月累计的数据，但上期是1月1号到28号的数据，如果想让1月也显示出整月的累计数，可以加个判断逻辑，这样来写本月至今的上月的度量值：

在这里插入图片描述
如果使用日期智能函数，可写为：

收入 MTD1 =
CALCULATE(
    [收入],
    DATESMTD(
        TREATAS({TODAY()},'日期表'[日期]))
)

因为没有外部日期上下文，这里利用了TREATAS将TODAY视同为日期表里的日期，作为DATESMTD的上下文，这样就会返回从月初至今的日期列表，然后执行汇总计算。

TREATAS的第一个参数必须是个表，而日期函数返回一个值，所以在外面套个大括号{ }，就可以将值变成表。

同理本月至今的上月以及上年同期：

收入 MTD PM1 =
CALCULATE(
    [收入],
    DATESMTD(
        TREATAS({EDATE(TODAY(),-1)},'日期表'[日期]))
)
收入 MTD PY1 =
CALCULATE(
    [收入],
    DATESMTD(
        TREATAS({EDATE(TODAY(),-12)},'日期表'[日期]))
)

在这里插入图片描述

4.5 动态数据显示

参考《动态显示最近N天的数据》、《PowerBI动态显示最近N期的数据》、《动态计算任意粒度的上期数据》

4.5.1 动态显示最近N天的数据

在这里插入图片描述

  1. 制作一个简易日期表，用于切片器，可以命名为切片日期表

    切片日期表 = VALUES('日期表'[日期])

  2. 新建参数N，用于展示N天的数据

  3. 创建度量值，显示最近N天的数据。
    如果日期在切片器所选日期的N天范围内，就显示这个[收入]数据，否则返回BLANK。

最近N天 收入 =
VAR N=[参数N]
VAR slicerdate=SELECTEDVALUE('切片日期表'[日期])
RETURN
IF(
    AND(
        SELECTEDVALUE('日期表'[日期])<=slicerdate,
        SELECTEDVALUE('日期表'[日期])>slicerdate-N
    ),
    [收入]
)

在这里插入图片描述
如果想动态的显示最近N年、最近N月的数据，也是这个思路

4.5.2 动态显示最近N期数据

现在想添加一个维度，动态选择最近N期的数据（N天、N月、N季度等等），也就是时间粒度和期数都可以自由选择，并且根据不同的选择，动态呈现对应粒度的坐标轴。该怎么做呢？

在这里插入图片描述

  1. 完善日期表：为了不同的粒度排序的需要，在日期表中为月份、季度和年度都添加一个编号
    在这里插入图片描述
  2. 建立日期粒度表：根据模型中的日期表，分别生成年、季、月、日的粒度表，并利用UNION函数将他们合并在一起，建立一个新的日期粒度表。
    在这里插入图片描述
    将需要动态展示的粒度，整合到一列中，每一个日期都包含完整的粒度类型。
    在这里插入图片描述
  3. 创建日期粒度切片器：再将这个日期粒度表复制一份，以便用于切片器，而不会产生交叉筛选
  4. 将’日期粒度表’与’订单表’建立关系，用于切片的日期粒度表不要建立关系
    在这里插入图片描述
  5. 创建参数，创建度量值：
最近N期 收入 =
VAR T = [参数 N] // 参数：动态控制N期
VAR slicerdate = MAX('切片 日期粒度表'[粒度编号]) // 获取切片日期表的值
RETURN
IF(
    AND(
        MAX('日期粒度表'[粒度编号]) <= slicerdate, // 小于等于切片值
        MAX('日期粒度表'[粒度编号]) > slicerdate - T // 大于等于切片值-动态参数
    ),
    CALCULATE([收入], TREATAS(VALUES('切片 日期粒度表'[类型]), '日期粒度表'[类型]))
    // 利用TREATAS构建虚拟关系，将没有建立关系的'切片 日期粒度表'，视同'日期粒度表'
)
  1. 创建报表
    利用日期粒度表中的字段[粒度]和度量值[最近N期 收入]生成一个柱形图，并用 切片日期粒度表中的字段生成两个切片器即可。
4.5.3 动态计算任意粒度的上期数据

上一节使用了独立的日期表作为切片器，这个做法也可以进一步优化，改用建立关系的日期粒度表来作为切片器。原模型不变，这里将切片器中的字段都改为来自日期粒度表，并写一个度量值：

最近N期 收入 优化 =
VAR T = [参数 N]
VAR slicerdate = MAX('日期粒度表'[粒度编号])
RETURN
IF(
    AND(
        MAX('独立 日期粒度表'[粒度编号]) <= slicerdate,
        MAX('独立 日期粒度表'[粒度编号]) > slicerdate - T
    ),
    CALCULATE([收入], TREATAS(VALUES('独立 日期粒度表'[粒度]), '日期粒度表'[粒度]))
)

然后使用这个度量值作为柱形图的值，未建立的关系的独立日期粒度表作为坐标轴，同样可以实现此效果。对于这种动态的粒度，还可以计算上期和去年同期的数据：

收入 上年同期 =
CALCULATE(
    [收入],
    CALCULATETABLE(
        SAMEPERIODLASTYEAR('日期表'[日期]),
        TREATAS(VALUES('日期粒度表'[日期]),'日期表'[日期])
    ),
    ALL('日期粒度表')
)

将当前日期粒度表所筛选的日期范围，视同日期表的日期范围，并利用时间智能函数返回该范围的上年同期的时间段；为了避免日期粒度表同时筛选，用ALL来忽略掉它。

计算当前粒度的上期，同样可以使用上面度量值的思路，但是对于不同的粒度，计算上期用到的时间智能函数是不同的，所以还需要先判断当前所选择的粒度类型。

当粒度类型为年时，上期就是上年同期，所以直接用上面建的上年同期度量值，当为其他粒度类型，则通过DATEADD函数来相应的计算该粒度的上期的时间段。

收入 上期 = 
SWITCH(
    SELECTEDVALUE('日期粒度表'[类型]),
    "年",[收入 上年同期],
    "季",CALCULATE([收入],CALCULATETABLE(DATEADD('日期表'[日期],-1,QUARTER),TREATAS(VALUES('日期粒度表'[日期]),'日期表'[日期])),ALL('日期粒度表')),
    "月",CALCULATE([收入],CALCULATETABLE(DATEADD('日期表'[日期],-1,MONTH),TREATAS(VALUES('日期粒度表'[日期]),'日期表'[日期])),ALL('日期粒度表')),
    "日",CALCULATE([收入],CALCULATETABLE(DATEADD('日期表'[日期],-1,DAY),TREATAS(VALUES('日期粒度表'[日期]),'日期表'[日期])),ALL('日期粒度表'))    
)

在这里插入图片描述

4.6 非标准日历的计算

4.6.1 非标准日历的计算思路

参考《非标准日历的计算思路》

在 Power BI 中，时间智能函数（如 DATESMTD、SAMEPERIODLASTYEAR）通常基于自然日历（年/月/日）工作。但如果业务周期不是按照自然日历划分（例如，25天为一个业务周期），则无法直接使用这些函数。这时，就需要通过构建业务日历表，进行自定义计算。

  1. 构建业务日历表：以业务周期25天为例，在日期表中添加业务周期字段——期间（文本，便于阅读）、期数（数字，便于计算）
  2. 业务日期表与订单表建立关系
    在这里插入图片描述
  3. 计算指标
收入 = SUM('订单表'[销售额])

收入 上期 =										// 比当前期数少一期的日期收入，即为上期收入
VAR _period = SELECTEDVALUE('日期表'[期数])
RETURN
    CALCULATE(
        [收入],
        FILTER(
            ALL('日期表'),
            '日期表'[期数]=_period-1
        )
    )

收入 上年同期 =
VAR _period = SELECTEDVALUE('日期表'[期数])
RETURN
    CALCULATE(
        [收入],
        FILTER(
            ALL('日期表'),
            '日期表'[期数]=_period-12
        )
    )

环比 = DIVIDE([收入]-[收入 上年同期],[收入 上年同期])
同比 = DIVIDE([收入]-[收入 上年同期],[收入 上年同期])

在这里插入图片描述
以上主要利用的是期数指标，如果要计算xx至今，就需要计算到日粒度：

收入 本期至今 =									// 期数和当前上下文的期数相等，但小于等于当前日期的订单销售额之和。
VAR _period=SELECTEDVALUE('日期表'[期数])
VAR _date=MAX('日期表'[日期])
RETURN
    CALCULATE(
        [收入],
        FILTER(
            ALL('日期表'),
            '日期表'[期数]=_period &&'日期表'[日期]<=_date
        )
    )

收入 本月至今 =
VAR _month=SELECTEDVALUE('日期表'[月份])
VAR _date=MAX('日期表'[日期])
RETURN
    CALCULATE(
        [收入],
        FILTER(
            ALL('日期表'),
            '日期表'[月份]=_month &&'日期表'[日期]<=_date
        )
    )

在这里插入图片描述

以上才是普遍意义上的时间指标计算，即根据业务逻辑，按条件进行筛选汇总。时间智能函数内部也是这样的逻辑，只是把常用时间维度的业务计逻辑进行封装，就形成了时间智能函数。

如果要做成可以动态交互的模式，比如随意选择2019年10月1日到10月7日国庆期间，计算出上一个七天，也就是2019年9月24日到9月30日的数据，可以改为：

本期收入 = SUM('订单表'[销售额])
上期收入 =
VAR DateRangeLength = DATEDIFF(MIN('日期表'[日期]),MAX('日期表'[日期]) , DAY) 	// 计算当前期间的天数
VAR PreDateEnd = MIN('日期表'[日期])-1 										    // 计算上期的结束日期
VAR PreDateStart = PreDateEnd-DateRangeLength 								    // 计算上期的开始日期 
VAR PreDateRange = DATESBETWEEN('日期表'[日期], PreDateStart, PreDateEnd) 	    // 上个期间范围
RETURN
CALCULATE([本期收入], PreDateRange)
环比 = DIVIDE([本期收入]-[上期收入],[上期收入])

在这里插入图片描述

4.6.2 计算任意日期区间的环比（非连续期间）

对于非连续期间的处理，标准的日期智能函数往往捉襟见肘，比如选择某年3月、5月、9月份的数据，并将其与所选的上一期数据对比，也就是5月v s 3月，9月 vs 5月，这种需求如何计算呢？

  1. 建立标准日期表，案例以 [年度月份] 维度对事实表的 [利润] 进行汇总。
    在这里插入图片描述

  2. 创建度量值，计算上期利润。主要思路是对于选定年月，在其之前的年月中选取最后一个年月：

    利润.上期所选 =
VAR CurrentYearMonth = SELECTEDVALUE ( '日期表'[年度月份] ) 	//当前年月
VAR PreviousYearMonth =
    CALCULATE (
        MAX ( '日期表'[年度月份] ),							// 使用max得到上一期间
        ALLSELECTED ( '日期表'[年度月份] ), 					// 修改年月的筛选上下文为当前所选
        KEEPFILTERS( '日期表'[年度月份] < CurrentYearMonth )	// 保留当前筛选条件，确保只考虑当前所选年度月份之前的月份。
    ) 														
VAR MarginPreviousYearMonth =
    CALCULATE (
        [利润],
        '日期表'[年度月份] = PreviousYearMonth ,
        ALL('日期表')
    )
RETURN MarginPreviousYearMonth

    在 DAX 中，ALLSELECTED 函数用于清除当前上下文中对指定列或表的筛选，同时保留由用户交互（如切片器或筛选器）所施加的筛选。如果没有 ALLSELECTED，当用户选择了 201705，MAX(‘日期表’[年度月份]) 不会返回任何值，因为当前上下文中只有201705，而 KEEPFILTERS(‘日期表’[年度月份] < CurrentYearMonth) 会筛选出所有小于 201705 的年月。

    利润.上期所选 =
VAR CurrentYearMonth = SELECTEDVALUE ( '日期表'[年度月份] ) //当前年月
VAR PreviousYearMonth =
    CALCULATE (
        MAX ( '日期表'[年度月份] ),
        FILTER (
            ALL ( '日期表'[年度月份] ),
            '日期表'[年度月份] < CurrentYearMonth
        )
    ) // 计算所选年月中的上一期
VAR MarginPreviousYearMonth =
    CALCULATE (
        [利润],
        '日期表'[年度月份] = PreviousYearMonth ,
        ALL('日期表')
    )
RETURN MarginPreviousYearMonth

  3. 计算环比

    利润.所选环比差异 =
VAR fisrtselected = MINX ( ALLSELECTED ( '日期表'[年度月份] ), [年度月份] )
//找出所选的第一期，然后让第一期返回空
RETURN
IF ( SELECTEDVALUE ( '日期表'[年度月份] ) <> fisrtselected, [利润] - [利润.上期所选] )

在这里插入图片描述

本文转自 https://blog.csdn.net/qq_56591814/article/details/146774116，如有侵权，请联系删除。

2023-2025 Powered by Kai with ❤️