Microsoft Power BI 和 DAX - 计算非 YTD 度量的上一年周数的百分比变化 [英] Microsoft Power BI and DAX - compute percentage change across previous year week numbers for non YTD measures
问题描述
我在 Power BI 矩阵视觉对象中有一个场景，其中我有一个简单的度量，称为 SalesAmount.它是基于表中单个列的简单总和，称为 FactSales.
I have a scenario in a Power BI Matrix visual, where I have a simple measure, called SalesAmount. It is a simple sum based on a single column of a table, called FactSales.
我的模型中有一个名为 Dates2 的日期维度(日历)表.
I have a Date dimension (Calendar) table in my model called Dates2.
我开发了一份矩阵报告，如下所示.此报告使用 Dates2 表中的 WeekNumber.我还开发了另一个名为 SalesAmount_SWLY 的度量来获取 SalesAmount 度量上一年的同一周.我的周数周期是周一到周日(不是默认的周日到周六周期).
I have developed a matrix report as below. This report uses a WeekNumber from the Dates2 table. I have also developed another measure called SalesAmount_SWLY to get the SalesAmount measure for the same week in the previous year. My Week Number cycle is Monday to Sunday (not the default Sunday to Saturday cycle).
(以上2张图，第1张是实际报表，第2张只是为了给大家看一下，上一年对应周的度量值是如何存储的.)
(The first of the above 2 visuals is the actual report, the second one is just to show you, how the measure for the corresponding week of the previous year is stored.)
重要的是要注意，SalesAmount 度量不是累积的(非 YTD)，即对于一年的第 10 周，我们不需要将第 1 周添加到第 10 周的销售额，但只显示周-10 销售额.但年份总数是在给定年份的所有周中汇总的.
It is important to note that the measure, SalesAmount is NOT cumulative (non-YTD), i.e. for Week-10 of a year, we do NOT need to add Week-1 to Week-10 sales, but show only Week-10 sales. But the year totals are aggregated across all the weeks for a given year.
然后，我制定了一项措施，以在平行的几周内生成百分比变化.例如，将 2020 年第 1 周与 2019 年第 1 周进行比较，得出百分比变化.
I then develop a measure to generate the percentage change, in parallel weeks. For example, Week-1 of 2020 is compared with Week-1 of 2019, to develop the % change.
2020 年第 1 周的百分比变化公式 = {(2020 年第 1 周的值)-(2019 年第 1 周的值)}/(2019 年第 1 周的值)
Formula for % change in Week-1 of 2020 = {(Value in Week-1 of 2020) - (Value in Week-1 of 2019)}/ (Value in Week-1 of 2019)
2020 年百分比变化的公式 = {(2020 年所有周的总和)-(2019 年所有周的总和)}/(2019 年所有周的总和)
Formula for % change in 2020 = {(Total of all the weeks of 2020) - (Total of all the weeks of 2019)}/ (Total of all the weeks of 2019)
这很好用，但有一个问题.在 2021 年，我们只有 2 周的数据.(假设到今天，我们在 2021 年只完成了 2 周，即使我们是在 9 月)
This works fine, but for one issue. In the year 2021, we have data only for 2 weeks. (Assume that as of today, we have completed only 2 weeks in 2021, even though we are in September)
就百分比变化而言，对于 2021 年的总和(右侧矩阵的最后一列)，我们需要将 2021 年前 2 周的总和与相应的总和进行比较2020 年前两周.
As far as the percentage change is concerned, for the aggregated total of 2021 (the last column of the matrix on the right side), we need to compare the aggregated total of the first 2 weeks of 2021, with the corresponding total of the first two weeks of 2020.
在上图中，我不想看到 -95.66%，而是 -18.75%.
In the visual above, I do not want to see -95.66%, but -18.75%.
逻辑:2021 年我们只完成了两周
Logic: we have completed only two weeks in 2021
2021 年的合计值 = 13(即 6 + 7)
Aggregated value in 2021 = 13 (i.e. 6 + 7)
2020 年的合计值 = 16(即 1 + 15，仅 2020 年的前两周)
Aggregated value in 2020 = 16 (i.e. 1 + 15, for the first two weeks alone in 2020)
(目前2020年取值为300，不正确)
(Currently the 2020 value is taken as 300, which is incorrect)
百分比变化 = (13-16)/16 = -18.75 %(报告中需要)
百分比变化 = (13-300)/300 = -95.66 %(目前在报告中显示，不正确)
几个月前我发布了一个类似的问题，关于不同年份的月份变化百分比:
I posted a similar question a few months back, for percentage change across months across different years:
微软Power BI - DAX 时间智能度量 - 更改上下文以反映适当的百分比变化；非 YTD 措施
有人可以使用 DAX 中的新度量来帮助我解决周数中的百分比变化问题吗?
Can someone help me solve this percentage change issue across Week Numbers, using a new measure in DAX ?
逻辑:
- 我使用从周一到周日的 7 天周期来计算 Dates2 表中的周数.Dates2 表中的列名称为 WeekNumber.
我使用以下公式计算去年同一周数的度量:
I compute the measure for the same week number last year, using the below formula:
SalesAmount_SWLY = CALCULATE(
[SalesAmount],
FILTER(
ALL(Dates2),
Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
&&
Dates2[WeekNumber] = SELECTEDVALUE(Dates2[WeekNumber])
)
)
我可以很好地计算不同年份的各个星期之间的百分比变化，例如 2020 年的第 5 周和 2019 年的第 5 周.类似的任何连续两年的相应周数.
I am fine in the way I compute percentage change between individual weeks of different years, say Week-5 of 2020 and Week-5 of 2019. Similary the corresponding week numbers of any two consecutive years.
我在计算最近一年(2021 年)和上一年(2020 年)之间的年度(总)百分比变化时遇到了挑战(这是因为，在 2021 年，我们还没有完成所有周)
I have a challenge in computing the yearly (grand) percentage change between the most recent year (2021), and the previous year (2020) (this is because, in 2021, we have not completed all the weeks)
我在 2021 年只有n"周.2021 年所有n"周的总和还不错；n＜53(大多数情况下，直到全年结束).
I have just 'n' weeks in 2021. The grand total for 2021 across all the 'n' weeks is fine; n < 53 (mostly, until the full year is completed).
我去的是上一年 2020.求 2020 年第 n 周第 7 天的日期.让我把这个日期称为 X.这被认为是结束日期上一年(2020 年).
I go to the previous year 2020. Find the date of the 7th day of the nth week of 2020. Let me call this date X. This is considered the end date for the previous year (2020).
2020 年的总计是从 1 月 1 日到 X 日计算的.该值用于计算 2020 年到 2021 年的年度百分比变化.
The grand total for 2020, is computed from Jan 1 to date X. This value is used to compute the yearly percentage change from 2020 to 2021.
对于连续年份的年度(总)百分比变化，我需要取这两年的总计，只有当这两个年份都有 53 周的数据时.最近一年(比如 2021 年)，我通常不会有所有的星期.在这种情况下，2021 年至 2020 年(仅限)，我只比较前n"周的数据.对于所有其他情况，一切正常.
For the yearly (grand) percentage change across consecutive years, I need to take the grand total for both the years, only when both years have 53 weeks of data. For the most recent year (say 2021), I will most often not have all the weeks. In this case, between 2021 and 2020 (only), I compare the data only for the first 'n' weeks. For all other cases, things are normal.
并且需要在代码中考虑以下逻辑:
And this below logic needs to be accounted in the code:
假设 2016 年，我有全部 53 周，但在 2017 年我只有前 50 周(第 51 周或第 52 周或第 53 周没有数据)，因为没有记录最后 3 周的数据2017.
Say 2016, I have all 53 weeks, but in 2017 I have only the first 50 weeks (no data in Week-51 or Week-52 or Week-53), because no data was recorded for the last 3 weeks of 2017.
当我在 2017 年和 2016 年之间进行百分比比较时，在这种情况下，我需要 2017 年矩阵的总和来使用这两年的所有周，而不是仅停留在 2016 年的 50 周.
When I do the % comparison between 2017 and 2016, in a case like this, I need the grand total of the matrix for 2017 to use all the weeks of both the years, and NOT stop with only 50 weeks of 2016.
本质上，我需要将前一年的周数缩短的主要逻辑，仅适用于本年，即 2021 年，并且当我们尚未完成本年的所有 53 周时.
In essence, I need the main logic of cutting the weeks short for the previous year, ONLY for the current year, i.e. 2021, and when we are yet to complete all the 53 weeks of the current year.
推荐答案
SalesAmount_SWLY =
VAR LastYear = YEAR(
MAXX(
ALL(FactSales),
FactSales[Date]
)
)
VAR LastDateOfRecordedSales = MAXX(
FILTER(
ALL(FactSales),
YEAR(FactSales[Date]) = MAX(Dates2[Year])
&&
ISBLANK(FactSales[Sales]) = FALSE
),
FactSales[Date]
)
VAR MaxWeekNumber = IF(
MAX(Dates2[Year]) = LastYear,
WEEKNUM(LastDateOfRecordedSales, 2),
53
)
VAR SWLY = CALCULATE(
[SalesAmount],
FILTER(
ALL(Dates2),
Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
&&
Dates2[WeekNumber] = SELECTEDVALUE(Dates2[WeekNumber])
)
)
VAR SWLYtotal = CALCULATE(
[SalesAmount],
FILTER(
ALL(Dates2),
Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
&&
Dates2[WeekNumber] <= MaxWeekNumber
)
)
RETURN
IF(
(HASONEVALUE(Dates2[Year]) = TRUE && HASONEVALUE(Dates2[WeekNumber]) = TRUE),
SWLY,
IF(
(HASONEVALUE(Dates2[Year]) = TRUE && HASONEVALUE(Dates2[WeekNumber]) = FALSE),
SWLYtotal,
BLANK()
)
)
这篇关于Microsoft Power BI 和 DAX - 计算非 YTD 度量的上一年周数的百分比变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
