Forum Discussion
Helper III7 days rolling average for missing values
Hi Harry_Tran ,
Sorry for late reply. You can try a calculated column like this:
index = RANKX(ALLSELECTED('CASE'[Date]),'CASE'[Date],,ASC)Result:Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IIIHello Yuna Anonymous ,
Thank you so much for your time.
The issue that I have using your approach is I have Categories, SubCategories, and Product. So, if I create an Index column I will look like this. Is there anyway that I can create an index column that have the same number for the same date?
I really appreciate your help
Super User
I've been experimenting with an approach that uses ranked dates by dimension. Here's what I have so far. The idea is to
1. Create a calculated table that summarizes each combination of fact table dimensions and date.
2. Create a calculated column that ranks the date by each combination of fact table dimensions (Categories in my example).
3. Create a measure that gets the date rank for the Categories/Date combination in each row of the matrix visual, subtract 6 from the date rank, and calculate the measure for the dates in this range.
Calculated table:
MovingAvgDates =
SUMMARIZE ( MovingAvg, MovingAvg[Categories], MovingAvg[Date] )
Calculated column (in calculated table):
Date Rank =
VAR vCategory = MovingAvgDates[Categories]
VAR vTable =
FILTER ( MovingAvgDates, MovingAvgDates[Categories] = vCategory )
VAR vResult =
RANKX ( vTable, MovingAvgDates[Date],, ASC, DENSE )
RETURN
vResult
Pre-calculating the date rank should improve performance, since it's calculated during the report refresh, and not in each row of the matrix (in a measure). Hope this moves you forward.
- Harry_Tran
Hi DataInsights ,
Thank you for your help.
I created the calculated table name 'New_Table' and calculated column 'Rank_Date' like you said.
Then I created Rolling_AVG Measure
Rolling_AVG =
var Min_Date = MAXX(FILTER(ALL('New_Table'),
'New_Table'[Rank_Date] = SELECTEDVALUE('New_Table'[Rank_Date])-6),
'New_Table'[Date])
RETURN IF (MAX('New_Table') <=7,
AVERAGEX(FILTER(ALLSELECTED('New_Table')
,
'New_Table'[Date] <= MAX('New_Table'[Date))
,
[Avg_Points_per_User-per_Day])
,
SUMX(FILTER(ALLSELECTED('New_Table')
,
'New_Table[Date] >= Min_Date && 'New_Table[Date]<= Max('New_Table'[Date]))
,
[Avg_Point_per_user_per_Day])/7)
what I got isThe first seven days it gave me the same value as [Avg_Point_per_User-per_Day]. After that it gave me the blank values
- DataInsights
Here's a simpler solution. No calculated table is needed.
Create calculated column in MAIN table:
Rank_Date = VAR vCategory = MAIN[Categories] VAR vTable = FILTER ( MAIN, MAIN[Categories] = vCategory ) VAR vResult = RANKX ( vTable, MAIN[Date],, ASC, DENSE ) RETURN vResultCreate measure:
Rolling 7 Day Average = VAR vCurrentDateRank = MAX ( MAIN[Rank_Date] ) VAR vMinDateRank = vCurrentDateRank - 6 VAR vSumAvgPoint = CALCULATE ( SUM ( [Avg_Point_per_User-per_Day] ), ALL ( MAIN[Date] ), FILTER ( ALL ( MAIN[Rank_Date] ), MAIN[Rank_Date] >= vMinDateRank && MAIN[Rank_Date] <= vCurrentDateRank ) ) VAR vCountDays = IF ( vCurrentDateRank >= 7, 7, vCurrentDateRank ) VAR vResult = DIVIDE ( vSumAvgPoint, vCountDays ) RETURN vResult- Harry_Tran
Avg_Point_per_User_per_Day is a measure so my measure is
Rolling 7 Day Average = VAR vCurrentDateRank = MAX ( MAIN[Rank_Date] ) VAR vMinDateRank = vCurrentDateRank - 6 VAR vSumAvgPoint = CALCULATE ( SUM ( MAIn[Point] ), ALL ( MAIN[Date] ), FILTER ( ALL ( MAIN[Rank_Date] ), MAIN[Rank_Date] >= vMinDateRank && MAIN[Rank_Date] <= vCurrentDateRank ) ) VAR vCountDays = IF ( vCurrentDateRank >= 7, 7, vCurrentDateRank ) VAR vUser = DISTINCTCOUNT (MAIN[User]) VAR vResult = vSumAvgPoint/ vUser / vCountDays RETURN vResultHere is the result. I don't really understand the logic of the values.
I really appreciate your time helping me