创建日期表

  • 9 分钟

在 Power BI 中创建报表时，一个常见的业务需求是根据日期和时间进行计算。 组织希望了解其业务在几个月、几个季度、会计年度等期间的表现。 因此，正确设置这些时间导向的值的格式至关重要。 Power BI 可自动检测日期列和表；但是，在某些情况下，您需要采取额外步骤才能获取组织所要求格式的日期。

例如，假设您正在为组织的销售团队编制报表。 数据库包含用于销售、订单、产品等的表。 您注意到，其中许多表（包括“Sales”和“Orders”）都包含自己的日期列，如“Sales”和“Orders”表中的 ShipDate 和 OrderDate 列所示。 您的任务是编制按年和月划分的总销售额和订单表。 如何生成具有多个表的视觉对象并让每个表都引用自己的日期列？

语义模型摘录的屏幕截图，其中突出显示了 Sales 表的 ShipDate 和 Order 表的 OrderDate。

若要解决此问题，可以创建一个可由多个表使用的公用日期表。 以下部分将说明如何在 Power BI 中完成此任务。

创建公用日期表

可用于生成公用日期表的方法包括：

  • 源数据

  • DAX

  • Power Query

源数据

有时，源数据库和数据仓库已具有自己的日期表。 如果设计数据库的管理员的工作完成得出色，则可以使用这些表执行以下任务：

  • 确定公司假日

  • 分离日历和会计年度

  • 确定周末与工作日

源数据表已非常成熟，可以立即使用。 如果您有这样的表格，请将其纳入您的语义模型，忽略此部分中概述的任何其他方法即可。 我们建议您使用源日期表，因为除了 Power BI 之外，它还可能与您使用的其他工具共享。

如果没有源数据表，则可以使用其他方法来生成公用日期表。

DAX

可以使用 Data Analysis Expression (DAX) 函数 CALENDARAUTO() 或 CALENDAR() 来生成公用日期表。 CALENDAR() 函数根据在函数中作为参数输入的开始日期和结束日期返回连续的日期范围。 或者，CALENDARAUTO() 函数返回根据语义模型自动确定的连续、完整的日期范围。 开始日期被选为语义模型中存在的最早日期，结束日期是语义模型中存在的最晚日期加上已填充到会计月份的数据，您可以选择此会计月份将其作为参数包含在 CALENDARAUTO() 函数中。 在本示例中，使用 CALENDAR() 函数是因为您只想查看从 2011 年 5 月 31 日（“Sales”开始跟踪此数据的第一天）开始到接下来 10 年的数据。

在 Power BI Desktop 中，选择新建表，然后输入以下 DAX 公式：

Dates  = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))

Power BI 中 CALENDAR 公式的屏幕截图。

现在，您有一个可以使用的日期列。 但此列略微稀疏， 而且您只想查看年份、月份号、一年中的第几周以及星期几的列。 可通过选择功能区上的新建列并输入以下 DAX 公式来完成此任务，该公式将从“Date”表中检索年份。

Year = YEAR(Dates[Date])

使用 DAX 公式添加列的屏幕截图。

您可以执行相同的过程来检索月份号、周数和星期几：

MonthNum = MONTH(Dates[Date])

WeekNum = WEEKNUM(Dates[Date])

DayoftheWeek = FORMAT(Dates[Date], "DDDD")

完成后，表将包含下图所示的列。

DAX 表中最后几列的屏幕截图。

现在，您已使用 DAX 创建了公用日期表。 此过程仅将您的新表添加到语义模型中；您仍然需要在 date 表与 Sales 表和 Order 表之间建立关系，然后将您的表标记为语义模型的正式日期表。 但是，在完成这些任务之前，请确保考虑生成通用日期表的另一种方法：使用 Power Query。

Power Query

您可以使用 M 语言（用于在 Power Query 中构建查询的开发语言）来定义通用日期表。

在 Power BI Desktop 中选择转换数据，它会将您引导至 Power Query。 在左侧查询窗格的空白处，右键单击以打开下面的下拉菜单，并在其中依次选择新建查询 > 空查询。

在 Power BI 中生成新查询的屏幕截图。

在显示的新建查询视图中，输入以下 M 公式以生成日历表：

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

使用 M 公式编制日历表的屏幕截图。

对于您的销售数据，您希望开始日期反映您数据中的最早日期：2011 年 5 月 31 日。 此外，您想查看接下来 10 年的日期，包括将来的日期。 此方法可确保随着新销售数据的流入，您不必重新创建此表。 您还可以更改持续时间。 在本例中，您希望每天都有一个数据点，但也可以按小时、分钟和秒递增。 下图显示了结果。

列表形式的销售日历的屏幕截图。

成功完成该过程后，您会发现自己拥有了一个日期列表，而不是一个日期表。 若要更正此错误，请转到功能区上的转换选项卡，然后选择转换 > 为表。 顾名思义，此功能会将列表转换为表。 您还可以将此列重命名为 DateCol。

在 Power Query 编辑器中将列表转换为表的屏幕截图。

接下来，您希望向新表中添加列，以按年、月、周和日查看日期，以便可以在视觉对象中生成层次结构。 第一个任务是更改列类型，方法是选择列名旁边的图标，然后在显示的下拉菜单中选择日期类型。

将类型更改为日期的屏幕截图。

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

通过 Power Query 添加列的屏幕截图。

请注意，Power BI 添加了从 DateCol 中提取的所有年份的列。

使用 Power Query 对一个表添加列的屏幕截图。

对月、周和天完成相同的过程。 完成此过程后，表将包含下图所示的列。

“DateCol”、“Year”、“Month”、“Week of Year”和“Day Name”列的屏幕截图。

您现在已经成功使用 Power Query 生成了一个通用日期表。

前面的步骤展示了如何将表纳入语义模型。 现在，您需要将表标记为正式日期表，以便 Power BI 可以识别所有未来的值并确保格式设置正确。

标记为正式日期表

要将表标记为正式日期表，首先在字段窗格中找到新表。 右键单击表名，然后选择标记为日期表，如下图所示。

“标记为日期表”选项的屏幕截图。

通过将表标记为日期表，Power BI 可执行验证以确保数据包含零个空值、是唯一的并且包含一段时间内的连续日期值。 您还可以选择表中的特定列以将其标记为日期，这在表中包含许多列的情况下非常实用。 右键单击表，选择标记为日期表，然后选择日期表设置。将出现以下窗口，您可以在其中选择应将哪一列标记为日期。

“标记为日期表”对话框的屏幕截图。

选择标记为日期表将从该表的 Date 字段中删除自动生成的层次结构，您将该表标记为日期表。 对于其他日期字段，在建立该字段与日期表之间的关系或关闭自动日期/时间功能前，自动层次结构仍将存在。 您可以向公用日期表手动添加层次结构，方法是右键单击字段窗格中的 year、month、week 或 day 列，然后选择新建层次结构。本模块后面将详细介绍此过程。

生成视觉对象

若要在“Sales”和“Orders”表之间生成视觉对象，需要在这一新的公用日期表与“Sales”和“Orders”表之间建立关系。 这样您将能够使用新的日期表来生成视觉对象。 要完成此任务，请转到模型选项卡 > 管理关系，可在其中创建通用日期表与“Sales”和“Orders”表之间的关系，方法是使用 OrderDate 列。 以下屏幕截图显示此类关系的一个示例。

“创建关系”对话框的屏幕截图。

建立关系后，您可以通过您使用 DAX 或 Power Query 方法编制的公用数据表，生成自己的按时间划分的总销售额和订单数量视觉对象。

若要确定总销售额，需要添加所有销售额，因为“Sales”表中的 Amount 列仅关注每笔销售的收入，而不是总销售收入。 可通过使用以下度量值计算来完成此任务，这将在稍后的讨论中进行说明。 生成此度量值时将使用的计算如下：

#Total Sales = SUM(Sales[‘Amount’])

完成后，可以通过返回可视化效果选项卡并选择表视觉对象来创建表。 您想要按年和月查看总订单数和销售额，因此只希望包括日期表中的“Year”和“Month”列、OrderQty 列以及 #TotalSales 度量值。 当您了解层次结构后，还可以生成一个允许您从年向下钻取到月的层次结构。 对于本示例，您可以并排查看它们。 现在，您已成功创建具有公用日期表的视觉对象。

使用 DAX 的公用日期列的屏幕截图。