【Power BI】在 Power BI 中设计数据模型创建日期表

创建日期表

已完成

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

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

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

创建公用日期表

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

源数据

DAX

Power Query

源数据

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

确定公司假日

分离日历和会计年度

确定周末与工作日

源数据表已经成熟，可以立即使用。如果你拥有这样的表，请将其置于数据模型中，而不要使用本部分概述的任何其他方法。我们建议使用源日期表，因为可能与你在 Power BI 之外可能会使用的其他工具共享它。

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

DAX

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

在 Power BI Desktop 中，转到功能区上的“表”选项卡。选择“新建表”，然后输入以下 DAX 公式：

DAX复制

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

DAX复制

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

DAX复制

DAX复制

DAX复制

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

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

Power Query

可以使用 M 语言（一种用于在 Power Query 中生成查询的开发语言）来定义公用日期表。

在 Power BI Desktop 中选择“转换数据”，这将引导你进入 Power Query。在左侧“查询”窗格的空白处，右键单击以打开下面的下拉菜单，你将在其中选择“新建查询”>“空查询”。

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

MDX复制

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

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

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

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

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

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

现在，你已成功使用 Power Query 生成公用日期表。

前面的步骤演示如何将表置于数据模型中。现在，你需要将表标记为正式日期表，以便 Power BI 可以为所有将来的值识别它，并确保格式设置正确。

标记为正式日期表

将表标记为正式日期表的第一个任务是在“字段”窗格中找到新表。右键单击表名，然后选择“标记为日期表”，如下图所示。

将表标记为日期表时，Power BI 会执行验证，以确保数据不包含 null 值并具有唯一性，并且包含一段时间内的连续日期值。还可以选择表中的特定列以将其标记为日期，这在表中有许多列时很有用。右键单击表，选择“标记为日期表”，然后选择“日期表设置”。此时会显示以下窗口，你可以在其中选择应将哪一列标记为“日期”。

选择“标记为日期表”将从标记为日期表的表的“日期”字段中删除自动生成的层次结构。对于其他日期字段，在建立该字段与日期表之间的关系或关闭“自动日期/时间”功能前，自动层次结构仍将存在。可以通过右键单击“字段”窗格中的年、月、周或日列，然后选择“新建层次结构”，将层次结构手动添加到公用日期表中。本模块稍后会进一步讨论此过程。

生成视觉对象

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

生成关系后，可以使用通过 DAX 或 Power Query 方法开发的公用日期表来生成“按时间划分总销售额和订单数”视觉对象。

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

DAX复制

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