Forum Discussion
7 days rolling average for missing values
Hello everyone, I need help to calculate 7 Days Rolling Average for missing data.
I have MAIN table with Date, Users, Points, Categories, Subcategories, and Products columns. I also hace Calendar table.
My Measures:
Avg_Point_per_User_per_Day = SUM(MAIN[Points])
/
DISTINCTCOUNT(MAIN[User])
/
DISTINCTCOUNT(MAIN[Date])
7_Days_Rolling_Avarage =
VAR Last_Date = LASTDATE(Calendar[Date].[Date])
VAR RollingAvg =
AVERAGEX (
DATEBETWEEN (Calendar[Date].[Date],
DATEADD (Last_Date, -7,DAY),
Last_Date), [Avg_Point_per_User_per_Day])
RETURN IF (Last_Date > TODAY() , BLANK(), RollingAvg)
The issue that I have is
When I apply Categories to the table, I have some data that do not have data. So, The [7_Days_Rolling_Avarage] returns (47+ 68 + 43 + 49 + 65)/5 ; (68 + 43 + 49 + 65 + 59)/5
What I expect is it would skip the missing date and return (47+ 68 + 43 + 49 + 65 + 59 + 62)/7
I came with another measure
Rolling_7_Days =
VAR Sevendays = CALCULATETABLE ( TOPN (7,
SUMMARIZE (MAIN, MAIN [Date],
“point”, SUM (MAIN [POINTS]) / DISTINCTCOUNT (MAIN [Users])),
MAIN[Date] , DESC),
FILTER (ALL (MAIN[Date]) , MAIN[Date] <= SELECTEDVALUE (MAIN[Date])))
RETURN
SUMX( Sevendays, [points]) / COUTROWS (Sevendays)
This one worked but it took almost 10 minutes to pull out the data.
Please help,
Thank you
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.
19 Replies
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 Anonymous ,
Thank you for your response
I created an index column as you suggested and it gave me result like this
it jumped from 2 to 6 and skip 3,4,5
Do you know why?
Thank you
Hi Harry_Tran ,
Will it happen after you sort the date column?
Please select the index column and verify if the contained values are only 2 and 6.
Best Regards,
Yuna
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.
Hello 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
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.
Hi,
See if my solution here helps - Show Balance outstanding everyday even if data for everyday is not available. See the solution in the last para.
Thank you so much.
I try the measure but it give me the same values as Avg_Points_per_User_per_Day
Hi,
Could you share your raw data in a format that can be pasted in an MS Excel workbook? Alternatively, share the download link of your PBI file.
Here is my Pbix file
https://drive.google.com/file/d/1AD6jvzXOmE68rG-Mp-SK_-7v1CAYobZd/view?usp=sharing
Thank you!
