英文:
PowerBI DAX measure to restrict calculation to complete, 7-day weeks in current context
问题
最终目标是能够计算某物的平均每周值,同时仅考虑属于当前筛选上下文中完整包含的周的日期。作为一个渐进步骤,动态确定日期(及其相关的值)为完整周日和不完整周日也会有所帮助。
所以,举个例子,假设我们有一个连接到标准销售表的日历表。销售表非常简单,只有一个日期列,一个物品列和一个销售数量列:
日期 | 物品 | 销售数量 |
---|---|---|
2023-04-01 | A | 5 |
2023-04-01 | B | 5 |
2023-04-02 | A | 5 |
2023-04-03 | A | 5 |
2023-04-04 | A | 2 |
2023-04-05 | A | 2 |
2023-04-07 | A | 2 |
2023-04-08 | A | 2 |
等等。假设数据一直持续到月底 - 这是筛选上下文(例如,我们正在筛选日历表中的日期)。在这个特定的示例中,假设 ISO 标准(周从星期一开始),2023-04-01 和 2023-04-02 不属于完整的一周,但四月的其余部分包括 4 个完整的 7 天的周。
如您所见,从第 3 天开始每天都有 2 笔销售,所以每周的平均值是 2x7=14。一个更加天真和不正确的计算可能只是将整个月份相加(28x2 + 2x5),然后除以周数(30/7=4.2857...),得到一个值为 15.4。出于我不在此处详述的原因,这不是我想采用的计算方法。我想要丢弃 4 月的前两天(以及任何其他被截断的周),将金额相加,然后除以完整周的数量。
如果筛选上下文也包括了三月,那么 4 月 1 日和 2 日将属于上一周(2023 年的第 13 周),因此它们应该计入。在这种情况下,三月的前 5 天将被丢弃。
解决这个问题的非常简单的方法是,如果 Power BI 中的计算列是动态的,即它们响应筛选,那么可以在日历表中使用一个简单的动态 IsFullWeek 列作为筛选器。不幸的是,这不是 DAX 的工作方式,所以需要另一种方法。
我已经做到了以下这一步:
AVERAGEX(KEEPFILTERS(VALUES('Calendar'[IsoWeekOfYear])), CALCULATE(SUM('Sales'[Amount])))
这确实得到了每周的平均值,但我无法弄清楚如何筛选不完整的周,而且老实说,我对 KEEPFILTERS 是如何工作的不是很确定。我尝试了这个筛选,但没有成功,而且我不明白为什么:
AVERAGEX(KEEPFILTERS(VALUES('Calendar'[IsoWeekOfYear])), CALCULATE(SUM('Sales'[Amount]), COUNT('Calendar'[IsoWeekOfYear]) = 7))
英文:
The end goal is to be able to calculate an AVERAGE weekly value for something while only considering days which are part of weeks which are fully included in the current filter context. As an incremental step, the dynamic determination of days (and their associated, related values) into complete-week-day and incomplete-week-days would also be helpful.
So, as an example, say we have a Calendar table joined to a standard Sales table. The Sales table is very simple, just a column for the date, a column for the item, and a column for the number sold:
Day | Item | Amount |
---|---|---|
2023-04-01 | A | 5 |
2023-04-01 | B | 5 |
2023-04-02 | A | 5 |
2023-04-03 | A | 5 |
2023-04-04 | A | 2 |
2023-04-05 | A | 2 |
2023-04-07 | A | 2 |
2023-04-08 | A | 2 |
And so on. Imagine the data continuing until the end of the month - that is the filter context (say, we're filtering on the Date in the Calendar table). In this specific example, assuming ISO standard (weeks start on Monday), 2023-04-01 and 2023-04-02 are not part of a complete week, but the rest of April comprise 4 full, entire 7-day weeks.
As you can see, there are 2 sales every day starting from the 3rd, so the average per week is 2x7=14. A more naive, incorrect calculation could for example just sum the entire month (28x2 + 2x5), divide by the number of weeks (30/7=4.2857...), and arrive at a value of 15.4. This is not the calculation I would like to go with, for reasons I won't get into here. I want to discard the first 2 days of April (and any other truncated weeks), sum the Amounts, and divide by the number of complete weeks.
If the filter context included March as well, April 1st and 2nd would be part of the previous week (the 13th week of 2023), and thus they should be counted. In that case, the first 5 days of March would be discarded.
The really simple way to solve this would be if calculated columns in PBI were dynamic, i.e. they responded to filters, which would mean a simple dynamic, IsFullWeek column in the Calendar table could be used as a filter. Unfortunately, that's not how DAX works, so another way is needed.
I've gotten as far as the following:
AVERAGEX(KEEPFILTERS(VALUES('Calendar'[IsoWeekOfYear])), CALCULATE(SUM('Sales'[Amount])))
This gets the average per week alright, but I can't figure out how to filter the incomplete weeks, and to be honest, I'm not entirely sure how it works w.r.t. the KEEPFILTERS. I tried this tp filter, but no luck, and I don't understand why.
AVERAGEX(KEEPFILTERS(VALUES('Calendar'[IsoWeekOfYear])), CALCULATE(SUM('Sales'[Amount]), COUNT('Calendar'[IsoWeekOfYear]) = 7)))
答案1
得分: 1
以下是您要翻译的内容:
销售:
切片器:
输出:
度量:
平均销售(整周)=
VAR m = CALCULATE(MIN(Sales[Day]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2) = 1))
VAR s = CALCULATE(MAX(Sales[Day]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2) = 7))
RETURN
AVERAGEX( DATESBETWEEN( 'Calendar'[Date], m, s), CALCULATE(SUM( Sales[Amount]) ) )
度量获取当前上下文中的第一个星期一和最后一个星期日。然后使用这两个日期来筛选AVERAGEX计算。
英文:
Here you go.
Sales:
Slicer:
Output:
Measure:
Average Sales (Whole Weeks) =
VAR m = CALCULATE(MIN(Sales[Day]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2) = 1))
VAR s = CALCULATE(MAX(Sales[Day]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2) = 7))
RETURN
AVERAGEX( DATESBETWEEN( 'Calendar'[Date], m, s), CALCULATE(SUM( Sales[Amount]) ) )
Measure gets the first Monday in the current context and the last Sunday in the current context. It then uses those two dates to filter the AVERAGEX calculation.
答案2
得分: 1
FCount_Fact =
// Avg F count Per ISO Week
// m,s: First Monday & Last Sunday of filtered timeframe. COALESCE needed for b
// b: Table of dates between m and s. If either blank, uses min/max of field, hence the COALESCE on m&s
// FCount: SUM(Flag)
// Flag is just 0 or 1 for every row depending on a filter - SUM(Flag) is a filtered count
// return: First calc. tbl of distinct week numbers in b. KEEPFILTERS on b necessary, I forgot why.
// Then calc SUM(Flag) - COALESCE needed for cases where there are no Fs in a week, this makes them count as 0 and not as NULL - 3wks of no Fs and 5 of 3 = 1.875 avg, not 3.
// KEEPFILTERS again using date filter keeps the week context (I think)
VAR m = COALESCE(CALCULATE(MIN('Calendar'[Date]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2)=1)),MIN('Calendar'[Date]))
VAR s = COALESCE(CALCULATE(MAX('Calendar'[Date]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2)=7)),MAX('Calendar'[Date]))
VAR b = DATESBETWEEN('Calendar'[Date], m, s)
RETURN
AVERAGEX(CALCULATETABLE(VALUES('Calendar'[IsoWeekOfYear]), KEEPFILTERS(B)), CALCULATE(COALESCE([FCount],0), KEEPFILTERS(b)))
这是我最终使用的度量标准。感谢Davide的建议,使用第一个星期一到最后一个星期日确定整个星期。
英文:
FCount_Fact =
// Avg F count Per ISO Week
// m,s: First Monday & Last Sunday of filtered timeframe. COALESCE needed for b
// b: Table of dates between m and s. If either blank, uses min/max of field, hence the COALESCE on m&s
// FCount: SUM(Flag)
// Flag is just 0 or 1 for every row depending on a filter - SUM(Flag) is a filtered count
// return: First calc. tbl of distinct week numbers in b. KEEPFILTERS on b necessary, I forgot why.
// Then calc SUM(Flag) - COALESCE needed for cases where there are no Fs in a week, this makes them count as 0 and not as NULL - 3wks of no Fs and 5 of 3 = 1.875 avg, not 3.
// KEEPFILTERS again using date filter keeps the week context (I think)
VAR m = COALESCE(CALCULATE(MIN('Calendar'[Date]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2)=1)),MIN('Calendar'[Date]))
VAR s = COALESCE(CALCULATE(MAX('Calendar'[Date]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2)=7)),MAX('Calendar'[Date]))
VAR b = DATESBETWEEN('Calendar'[Date], m, s)
RETURN
AVERAGEX(CALCULATETABLE(VALUES('Calendar'[IsoWeekOfYear]), KEEPFILTERS(B)), CALCULATE(COALESCE([FCount],0), KEEPFILTERS(b)))
This is the measure I ended up using. Credit to Davide for the idea of using the first monday-last sunday to determine whole weeks.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论