创建每周6个月的滚动平均值过滤条件 [英] Create 6 months rolling average on week filter
问题描述
我正在尝试创建一个报告,该报告应该提供每周数据,还应提供一个列,用于滚动显示6个月到上个月和去年同期。
I am trying to create a report, which should give weekly data but also a column for rolling 6 months till the last month and the same period last year.
我可以使用以下公式计算滚动平均值:
I am able to calculate the rolling average using the formula below:
6 months rolling =
VAR period_end =
CALCULATE(
MAX('Dimensions'[Month Start Date]),
FILTER(
ALL('Dimensions'[Year Week]),
'Dimensions'[Year Week]=SELECTEDVALUE('Dimensions'[Year Week])
)
)
VAR period_till =
FIRSTDATE(
DATESINPERIOD(
'Dimensions'[Month Start Date],
period_end,
-1,
MONTH
)
)
VAR period_start =
FIRSTDATE(
DATESINPERIOD(
'Dimensions'[Month Start Date],
period_till,
-6,
MONTH
)
)
RETURN
CALCULATE(
SUM(Total_Sales),
DATESBETWEEN(
[Month Start Date],
period_start,
period_till
)
)
出现数据很好,但是一旦我在[Year Week]上放了一个切片器,它就会开始提供每周数据,而不是滚动平均值。
The data comes up fine but as soon as i put a slicer on the [Year Week], it starts giving the weekly data, rather than Rolling average.
我想我需要使用ALL过滤器,但是我的努力还没有得到回报。感谢所有帮助。
I think i need to use ALL filter but my efforts haven't paid off on it too yet. Appreciate any help on this.
报告结构如下:
类别
Category
Current_Week_Data
Current_Week_Data
上一年同一周的数据
差异%
滚动6个月(今年-前6年6个月/上一年6个月)
rolling 6 months (this year - previous 6 year 6 months /previous year 6 months)
推荐答案
我认为这可以解决问题。如果我们在12月(如现在),则将对今年以及去年6月至11月的数据 [X]求和,然后计算出分数变化。明智的做法是,切片器不会在[Year Week]上受到影响
I think this will do the trick. If we are in Dec (as now) it will sum 'Data'[X] for June-Nov this year as well as last year, and then calculate the fractional change. It will not be affected, calculation wise, by a slicer on [Year Week]
Rolling n month average =
var n = 7 //number of months looking back
var currentDate = MAX('Calendar'[Date]) // max date in current context
var startDate = EOMONTH(EDATE(currentDate; -n);0) // end date of the 7th month back
var currentDateLY = currentDate-364 // Monday -> Monday, use 365 if date match is more important. Using 365 also removes strange values in the beginning/end of months.
var startDateLY = EOMONTH(EDATE(currentDateLY; -n); 0)
var theDataTY = // the data This Year
CALCULATE(
SUM('Data'[X]);
ALL('Calendar'[Year Week]);
FILTER(
ALL('Calendar'[Date]);
'Calendar'[Date] > ( startDate ) && 'Calendar'[Date] <DATE(YEAR(currentDate);MONTH(currentDate);1) // the 6 month interval
)
)
var theDataLY = // the data Last Year
CALCULATE(
SUM('Data'[X]);
ALL('Calendar'[Year Week]);
FILTER(
ALL('Calendar'[Date]);
'Calendar'[Date] > ( startDateLY ) && 'Calendar'[Date] <DATE(YEAR(currentDateLY);MONTH(currentDateLY);1) // the 6 month period last year
)
)
return
DIVIDE( // calculate the fractional change
theDataTY - theDataLY;
theDataLY;
0 // returns 0 if fraction is not defined.
)
我有两个桌子设置:日历和数据。 日历 [日期]和数据 [日期]之间存在1 ::的关系。另外,我毫不怀疑在DAX中有更好的方法可以做到这一点,这就是我会做到的方式。
I have two tables in my setup: 'Calendar' and 'Data'. There's a 1:* relationship between 'Calendar'[Date] and 'Data'[Date]. Also, I have no doubt that there is a better way of doing this in DAX, this is just how I would have done it.
希望这会有所帮助。
干杯,
奥斯卡
这篇关于创建每周6个月的滚动平均值过滤条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!