Calculate Relative Weeks in Power BI

问题

在实施任何Microsoft Business Intelligence（BI）项目时，Time Intelligence一直是一个有趣的话题 。可能是从大型企业到小型 Power BI项目。分析一段时间内的数据有助于我们了解在其他因素存在下特定主题的发生率如何变化。例如， 年复一年（YoY）或月初至今（MTD），所有这些计算都可以轻松地在DAX中获得年份，月份和季度。但是，当涉及到星期时，没有开箱即用的公式可以直接比较星期值。

在本文中，我将解释如何在SQL和Power BI中相对于当前星期生成相对星期的值 。本文假定您在开发Power BI数据模型和使用DAX处理数据方面具有一定的知识。

图1-相对周示例

在上面的图中，当前星期作为写这篇文章是8.因此，RelativeWeekNumber是0 为当前星期。同样，该值为 -1的前一周 和1为下周。

 

 

在此示例中，如果您想报告从今天开始的最后两周，则可以只使用过滤器“ WHERE RelativeWeekNumber < 0 AND RelativeWeekNumber >=-2”。

解

在本技巧中，我们将探索如何使用SQL和DAX生成相对周数。

使用SQL计算相对周

与在DAX或任何其他脚本中执行任何计算相比，操作SQL源要容易得多。为了本文的方便，我使用的是“日期维度”表，以及日期介于01-Feb-2020 和29-Feb-2020之间的日期。以下是有关“日期”维度表的简单选择语句。为了简单起见，我从表中仅选择了两列。

• 日期–返回表格中的完整日期
• WeekDayName –返回日期的工作日名称。
• ISOWeekOfYear –返回日期的ISO周编号。每周从星期一开始。

图2-从DimDate选择数据

一旦我们准备好了这些数据，就可以继续获取日期的星期数与当前日期之间的差。这可以通过从当前日期减去日期的星期数来完成。

例如， WEEKNUMBER('2020-02-19') – WEEKNUMBER(Current Date) = 0

两个实体之间的差异为0，因为两个日期的星期数相同。

 

让我们看看使用SQL的实际效果。

图3-添加相对周数

在上图中，您可以看到 已经计算了RelativeWeekNumber，并且当前周的值为0， 而上周和下周的值分别为-1和1。

另外，要注意的另一个重要点是，在此计算中， RelativeWeekNumber返回一个从星期日开始的新星期 ；一周开始不是ISO标准。如果您需要从星期一开始返回星期数 ，则可以如下修改脚本。

图4-添加相对ISO周编号

选择
   [Date] 
   ，[WeekDayName] 
   ，[ISOWeekOfYear] 
   ，DATEDIFF （WW ，GETDATE （），[Date] ）AS RelativeWeekNumber 
   ，DATEDIFF （WW ，GETDATE （），DATEADD （DD ，-1 ，[Date] ））AS RelativeISOWeekNumber 
FROM [dbo] 。[DimDate] 
WHERE [日期] BETWEEN'2020-02-01'和'2020-02-29'

在图4中，如果考虑列– RelativeISOWeekNumber，则可以注意到与先前计算的列（即RelativeWeekNumber）的区别。对于较早的日期，一周从星期一开始，而对于星期日的星期几开始。

在Power BI中使用DAX计算相对周

现在，我们已经了解了如何在SQL中计算相对星期数，让我们还探讨了如何使用DAX在Power BI中实现这一点。

出于本教程的目的，我将创建一个Power BI报表，并将其连接到Date Dimension表，然后再次从源中获取相同的列。

 

图5-将数据导入Power BI

导入数据后，我们可以可视化在Power BI中创建的表。

图6-可视化数据表

现在我们已经在Power BI中准备好了数据，下一步是继续创建创建用于存储StartOfWeek 和StartOfCurrentWeek值的计算列。下面提供了DAX脚本。

StartOfWeek = DimDate [Date]-WEEKDAY（DimDate [Date]，2）+ 1
StartOfCurrentWeek = TODAY（）-WEEKDAY（TODAY（），2）+ 1

图7-添加计算列

最后一步是从我们刚刚创建的两个计算列中计算相对星期值。这可以通过从 StartOfWeek值中减去 StartOfCurrentWeek，然后将天数除以7来完成。下面给出了计算RelativeWeek的DAX脚本。

RelativeWeek =（DimDate [StartOfWeek]-DimDate [StartOfCurrentWeek]）/ 7

图8-在DAX中计算相对周

在本文中，我们已经看到了如何使用DAX在SQL和Power BI中计算相对星期值。