What Are the Possibilities to Build Date Tables in Self-Service Environments?

Towards Data Science

Towards Data Science2026年6月21日

What Are the Possibilities to Build Date Tables in Self-Service Environments?

8.5内容质量

TL;DR · AI 摘要

在自助服务环境中，使用DAX代码构建日期表是常见方法，但也可通过其他方式实现，如在数据仓库中直接创建。

核心要点

使用DAX代码生成日期表时，可从数据模型中提取最小和最大日期。
在数据仓库中创建日期表可以提高灵活性和效率。
ADDCOLUMNS函数可用于向日期表添加年、月、日等列。

结构提纲

按章节快速跳转。

§引言
作者长期使用DAX代码创建日期表，但最近发现其他方法。
·数据仓库中的日期表
在数据仓库中创建日期表可以提高灵活性和效率。
·DAX表格
使用DAX代码生成日期表时，可从数据模型中提取最小和最大日期。
›ADDCOLUMNS函数
ADDCOLUMNS函数可用于向日期表添加年、月、日等列。

思维导图

用一张图看清主题之间的关系。

查看大纲文本（无障碍 / 无 JS 友好）

构建日期表的方法
- 数据仓库
  - 提高灵活性和效率
- DAX代码
  - 使用CALENDAR()函数设置起止日期
  - 使用ADDCOLUMNS()函数添加列

金句 / Highlights

值得收藏与分享的关键句。

在数据仓库中创建日期表可以提高灵活性和效率。
— 第 2 段
⬇︎ 下载 PNG 𝕏 分享到 X
使用DAX代码生成日期表时，可从数据模型中提取最小和最大日期。
— 第 3 段
⬇︎ 下载 PNG 𝕏 分享到 X
ADDCOLUMNS函数可用于向日期表添加年、月、日等列。
— 第 4 段
⬇︎ 下载 PNG 𝕏 分享到 X

#DAX#数据仓库#Power Query#日期表

打开原文

在自助服务环境中构建日期表的可能性 | Towards Data Science

数据工程

在自助服务环境中构建日期表的可能性

多年来，每当无法在数据流上游创建日期表时，我都会使用 DAX 代码来创建日期表。现在，我意识到还有另一种方法可以实现这一点。让我们看看有哪些替代方案以及它们之间的比较。

Salvatore Cagliari

2026年6月21日

11分钟阅读

照片由 Javier Allegue Barros 在 Unsplash 上提供

介绍

多年来，当没有其他来源可以创建这样的表格时，我一直在使用 DAX 代码在表格模型中构建日期表。

我创建了一个模板代码，并反复使用它。它在许多情况下都能很好地工作。

我将其分发给我的客户，他们都对它感到满意。

但大约两周前，我和一位同事进行了一次讨论，这让我意识到了一种我之前从未考虑过的方法。

因此，让我们看看构建日期表的各种方法并进行比较。

但无论采用哪种方法，了解语义模型中日期表的要求都是非常重要的。

当存在数据仓库时会发生什么？

首先，当我有一个数据存储和语义模型的来源，无论是关系数据库、Fabric Lake 还是任何其他集中的数据存储时，我会在那构建它并在语义模型中使用它。

在那种情况下，构建这种表格的选项非常广泛且灵活，DAX 和 Power Query 都没有更高效。

因此，在这种情况下，如何构建它没有疑问。

DAX 表

在 DAX 中生成日期表相对容易且直接。

DAX 提供了大量函数，可以向日期表添加列和功能。

你总是从 CALENDAR() 调用开始，以设置起始和结束日期。

你可以使用固定值，例如基于可用数据的 MIN() / MAX() 调用，从数据模型中的数据表中获取起始和结束日期，或者使用一些（Power Query）参数。

例如，如下所示：

code

DimDate = 
CALENDAR (
    DATE ( YEAR (
        MIN ( 'Online Sales Order'[Date] )
    ), 1, 1 ),
    DATE ( YEAR (
        MAX ( 'Online Sales Order'[Date] )
    ), 12, 31 )
)

由于微软要求日期表中包含完整的年份，我从一月一日开始，到十二月三十一日结束。

接下来，你可以添加更多列，将年份、季度、月份和天数添加到表中。

你可以在表的定义中使用 ADDCOLUMNS() 来实现这一点：

code

DimDate = 
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( MIN ( 'Online Sales Order'[Date] ) ), 1, 1 ),
        DATE ( YEAR ( MAX ( 'Online Sales Order'[Date] ) ), 12, 31 )
    ),
    "Date_ID", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonth_ID", CONVERT ( FORMAT ( [Date], "YYYYMM" ), INTEGER ),
    "YearMonth", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "MonthDate", EOMONTH ( [Date], 0 ),
    // 用户自定义格式字符串 mmm yyyy（短月份）或 mmmm yyyy（长月份），
    "DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "IsWorkday", IF ( WEEKDAY ( [Date] ) IN { 1, 7 }, 0, 1 ),
    "SemesterNumber", IF ( INT ( FORMAT ( [Date], "MM" ) ) <= 6, 1, 2 ),
    "Semester", IF ( INT ( FORMAT ( [Date], "MM" ) ) <= 6, "S1", "S2" ),
    "YearSemesterNumber",
        IF (
            INT ( FORMAT ( [Date], "MM" ) ) <= 6,
            YEAR ( [Date] ) * 10 + 1,
            YEAR ( [Date] ) * 10 + 2
        ),
    "YearSemester",
        IF (
            INT ( FORMAT ( [Date], "MM" ) ) <= 6,
            FORMAT ( [Date], "YYYY" ) & "/S1",
            FORMAT ( [Date], "YYYY" ) & "/S2"
        ),
    "QuarterNumber", INT ( FORMAT ( [Date], "q" ) ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarterNumber",
        YEAR ( [Date] ) * 10 + FORMAT ( [Date], "Q" ),
    "YearQuarter",
        FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
    "DayOfMonth", FORMAT ( [Date], "DD" ),
    "DayOfYear", DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
    "DayOfYear_woWeekend", NETWORKDAYS ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], 1 ),
    "RestDaysInYear",
        DATEDIFF (
            DATE ( YEAR ( [Date] ), 1, 1 ),
            DATE ( YEAR ( [Date] ), 12, 31 ),
            DAY
        )
            - DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
    "RestDaysInYear_woWeekend",
        NETWORKDAYS (
            DATE ( YEAR ( [Date] ), 1, 1 ),
            DATE ( YEAR ( [Date] ), 12, 31 ),
            1
        )
            - NETWORKDAYS ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], 1 ),
    "WeekNumber", WEEKNUM ( [Date], 21 )
)

有趣的是，可以将名称或区域设置传递给 FORMAT() 函数，例如，用不同语言创建月份名称：

code

DimDate = 
ADDCOLUMNS(
    CALENDAR(DATE(YEAR(MIN('Online Sales Order'[Date])), 1, 1)
            ,DATE(YEAR(MAX('Online Sales Order'[Date])), 12, 31)
            ),
"Date_ID", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonth_ID", CONVERT(FORMAT ( [Date], "YYYYMM" ), INTEGER),
"YearMonth", FORMAT ( [Date], "YYYY/MM" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameShort_DE", FORMAT ( [Date], "mmm", "de-de" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"MonthNameLong_DE", FORMAT ( [Date], "mmmm", "de-de" ),
"DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeek_DE", FORMAT ( [Date], "dddd", "de-de" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"DayOfWeekShort_DE", FORMAT ( [Date], "ddd", "de-de" )
)

图1 – 使用 DAX 创建的日期表，其中包含多语言列（图片由作者提供）

请注意 FORMAT() 函数的第三个参数 “de-de” 以及表中对应的列，一列是英文，一列是德文。

但随着用户上下文感知的计算列的出现，也可以用另一种方式实现。

如需了解有关此新功能的更多信息，请阅读此处。

如果你需要计算逻辑更为复杂的列，可以使用计算列，并通过上下文转换来访问整个表。

如果你还不了解上下文转换，可以阅读这篇解释该概念的文章：

DAX 中上下文转换的妙处

一个例子是计算财政年度的周数，当财政年度与日历年不一致时。

用数学公式来实现这一点简直是一场噩梦，或者我的数学技能还不够高超。

Power Query 和数据流

现在我们来到最后一个变体：使用 Power Query 或数据流。

首先，我不区分 v1 或 v2 中的 Power Query 和数据流，因为它们都基于相同的原则，并使用相同的语言。

我从在 Power Query 中创建三个参数开始构建日期表：

StartYear：日期表中的第一个年份

YearsToLoad：日期表应覆盖的年份数

FirstMonthOfFiscalYear：财政年度的第一个月。如果财政年度与日历年一致，该值为 1；否则，该值为财政年度第一个月的编号。

所有后续代码都将依赖这些参数。

开始总是使用相同的命令：List.Dates()

此函数的参数为：

起始日期

创建列表的天数

间隔，此处为天数

这将产生如下所示的一行代码，同时使用上述提到的参数：

code

List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0))

接下来是第一个障碍：

通常，我们需要一个跨越多年份的日期表。但每四年中有一个闰年。

那么，我们如何实现这一点，因为微软要求日期表必须覆盖完整的年份？

解决方案是获取最后一年的最后日期（12 月 31 日），然后筛选行，仅保留早于或等于该日期的行。

这就是为什么我要将 366 天乘以参数 YearsToLoad 的原因。

以下是此场景的完整 M 代码：

code

let
    Source = List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Last Valid Date" = Table.AddColumn(#"Changed Type", "Last Valid Date", each #date(Date.Year(List.Max(#"Changed Type"[Date])) - 1, 12, 31), type date),
    #"Keep only valid dates" = Table.SelectRows(#"Added Last Valid Date", each [Date] <= [Last Valid Date])
in
    #"Keep only valid dates"

接下来，我可以开始添加所有需要构建完整日期表的列。

首先，我添加一个 Date_ID，用数字表示日期：

code

Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date])

此列必须设置为整数数据类型。因此，整个 M 代码行如下：

code

Table.AddColumn(#"Keep only valid dates", "Date_ID", each ( Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date]), Int64.Type)

请注意在最后一个右括号之前的 Int64.Type 表达式。这可以在同一命令中设置数据类型，从而避免了额外的步骤。

接下来，我可以使用 Power Query 编辑器中的功能，添加一些我经常添加到日期表中的列：

图 2 – 基于日期列添加列的内置功能。通过选择日期列并进入“添加列”选项卡，可以获取该功能。（图由作者提供）

如你所见，我们可以在不编写代码的情况下添加大量列。

但在某些时候，我们必须编写自己的代码来添加额外的列，例如用于存储年份和对应期间的列。

以下是一些这些列的示例：

年/月名称

年/季度

年/周

然后，对于任何期间（如周或月）的开始和结束日期的列。

我使用这些列在 DAX 中编写自定义的时间智能代码。我之前在其他文章中也写过一些相关内容，例如每周计算。

在某些情况下，常规的 M 代码不足以获取所需的信息。

例如，当我需要获取一个与周对齐的年份列（YearForWeek）时。

对于这些场景，我开始编写自定义的 M 函数，这些函数允许我访问每行的日期范围，这在 M 中是无法实现的。

在这种情况下，我添加了以下函数：

code

(DateInput as date) as number =>
let
    ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),
    Year = Date.Year(ClosestThursday)
in
    Year

如果你对自定义的 M 函数不熟悉，我强烈建议你了解这个强大的功能。

我将在下面的参考资料部分中添加一些链接。

在完成日期表的全部开发后，我得到了这些自定义函数：

GetISOYear 获取与周对齐的年份

GetISOWeek 根据 ISO 标准计算正确的周数

CalculateMonthDiff 计算两个日期之间的月份差

CalculateQuarterDiff 计算两个日期之间的季度差

GetFiscalWeekNumber 计算以财政年开始的那周的周数

GetCurrentFiscalYear 根据当前日期获取当前财政年份

GetCurrentFiscalStartYear 计算当前财政年份开始的年份

这花了我一些时间（2–3 天的工作），但我成功地将所有列整合到了日期表中，我认为这在大多数情况下都是有用的。

但是，M 语言中的基本概念增加了额外的工作和复杂性，这在 SQL 中是不需要的。

但为了不在此处复制所有的 M 代码，我将为你提供一个包含整个解决方案和日期表的 Power BI 文件。

接下来该做什么？

现在，你可以将整个 M 代码复制到数据流中，并在组织内部共享。

为了允许访问你的数据流，只需在工作区中向消费者授予查看者权限即可。

这样，你就可以拥有一份所有人都可以使用的中央统一版本的日期表。

这是使这种方法非常有用的主要原因。

这与你拥有一个中央数据平台并构建日期表的情况是一样的。但并非每个人都有这样的平台，因此使用数据流是一个很好的折中方案。

在我使用数据流的工作中，我发现排查导入失败的问题可能会很麻烦。我发现错误信息往往非常有限，可能还会遗漏重要的细节。

该使用哪一种？

我建议使用哪一种？

首先，当你有一个集中的数据存储，无论是在本地还是基于云，或者它是关系型数据库或其他类型的数据存储时，使用它来构建日期表。

正如我之前提到的，这一点毫无疑问。

在自助式 BI 场景中，或者当公司规模不大时，这个决定就没有那么直接了。

首先，这取决于可用的技能。

在 Power Query 中构建日期表后，我发现使用 DAX 来构建日期表比在 Power Query 中使用 M 代码要容易得多。

DAX 的功能使其比在 Power Query 中使用 M 代码更容易构建日期表。

我可以在一个 DAX 语句中定义表，并在额外的计算列中添加复杂的逻辑。

但是，每个 DAX 日期表都只属于每个 Power BI 语义模型。因此，你最终可能会有多个日期表，它们之间可能会出现差异。

但一旦你有多个团队在构建 Power BI 解决方案，创建一个中央日期表并将其共享给所有团队可能会带来好处。

当有人需要在日期表中添加一个新功能时，它将被添加到中央表中，所有人都可以从中受益。

当然，这适用于任何类型的集中式日期表。

在这种情况下，数据模型开发人员始终可以决定要导入哪些列，从而避免将不必要的列导入数据模型。

结论

现在你知道了构建日期表的不同方法。

你可以在可用的选项中做出选择。

但从本地 DAX 表切换到任何集中式表将会很困难。

你应该尽早考虑选择哪种方式，以避免在它们之间切换时带来的额外工作。

花点时间，与所有团队成员或潜在模型创建者进行讨论，选择正确的方式。

如果没有人使用集中式日期表，那么决定构建它就没有意义。

因此，请确保每个人都支持使用集中式日期表。

参考资料

这里有关于 M 中自定义函数的微软文档：

https://learn.microsoft.com/en-us/powerquery-m/m-spec-functions

微软 Learn 上有关于自定义函数的页面：

https://learn.microsoft.com/en-us/power-query/custom-function

Wicked Smart Data 的一篇很好的解释：

https://www.wickedsmartdata.com/articles/custom-m-functions-power-query

如果你更喜欢通过视频学习，这个视频从头开始解释了自定义函数：

这个视频提出了自定义函数何时有用的问题：

这个视频展示了你如何解决一个挑战，包括一个非常实用的方法，教你如何轻松开发自定义函数：

作者

查看 Salvatore Cagliari 的所有文章

数据科学

,

DAX

Power BI

Power BI 教程

Power Query

分享这篇文章

在 Facebook 上分享

在 LinkedIn 上分享

在 X 上分享

Towards Data Science 是一个社区出版物。提交你的见解，以触及我们的全球受众，并通过 TDS 作者支付计划获得报酬。

将 href 更新为你的实际提交 URL

为 TDS 写作

✦ 结束 CTA ✦