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.
Hi Harry_Tran ，
Based on your description, you can do some steps as follows.
- Create an index column.
2. Create a measure.
Measure =
var min_date=MAXX(FILTER(ALL('Case'),[Index]=SELECTEDVALUE('Case'[Index])-6),'Case'[Date])
return
IF(
MAX('Case'[Index])<=7,
AVERAGEX(FILTER(ALLSELECTED('Case'),[Date]<=MAX('Case'[Date])),[Avg_Point_per_User-per_Day]),
SUMX(FILTER(ALLSELECTED('Case'),'Case'[Date]>=min_date&&'Case'[Date]<=MAX('Case'[Date])),[Avg_Point_per_User-per_Day])/7)
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.
Harry_TranHello 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
- DataInsights
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 vResultPre-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
