创建每周过滤器的 6 个月滚动平均值

本文介绍了创建每周过滤器的 6 个月滚动平均值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试创建一个报告，该报告应提供每周数据，但还应提供一列，用于滚动 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.

报告结构是这样的:

类别

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 月以及去年的 'Data'[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. )

我的设置中有两个表:日历"和数据".'Calendar'[Date] 和 'Data'[Date] 之间存在 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.

希望这会有所帮助.

干杯，

奥斯卡