7 days rolling average for missing values

[Harry_Tran]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

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.

[Harry_Tran]

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

[Anonymous]

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

[Harry_Tran]

 @Ashish_Mathur 

Here is my Pbix file

https://drive.google.com/file/d/1AD6jvzXOmE68rG-Mp-SK_-7v1CAYobZd/view?usp=sharing

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

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.

[Harry_Tran]

Hi @Ashish_Mathur 

Thank you so much.

I try the measure but it give me the same values as Avg_Points_per_User_per_Day

[Ashish_Mathur]

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.

[Anonymous]

Hi @Harry_Tran ，

Based on your description, you can do some steps as follows.

1. 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_Tran]

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

[DataInsights]

@Harry_Tran,

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 is

The 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]

@Harry_Tran,

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
    vResult

Create 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]

@DataInsights 

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
    vResult

Here is the result. I don't really understand the logic of the values.

I really appreciate your time helping me 

[DataInsights]

@Harry_Tran,

Change your measure to the following. You don't need SUM in the CALCULATE function, since the expression is a measure (it's a column in my test data, hence the SUM).

Rolling 7 Day Average =
VAR vCurrentDateRank =
    MAX ( MAIN[Rank_Date] )
VAR vMinDateRank = vCurrentDateRank - 6
VAR vSumAvgPoint =
    CALCULATE (
        [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 vUser =
    DISTINCTCOUNT ( MAIN[User] )
VAR vResult = vSumAvgPoint / vUser / vCountDays
RETURN
    vResult

[Harry_Tran]

@DataInsights 

My [Avg_Point_per_User_per_Day] is calculated by

Avg_Point_per_User_per_Day = 
        SUM(MAIN[Point])
       /
        DISTINCTCOUNT(MAIN[User])
       / 
        DISTINCTCOUNT (MAIN[Date])

I want to calculate the Rolling 7 days average of the [Avg_Point_per_User-Per_Day]

I use your measure above

Rolling 7 Day Average =
VAR vCurrentDateRank =
    MAX ( MAIN[Rank_Date] )
VAR vMinDateRank = vCurrentDateRank - 6
VAR vSumAvgPoint =
    CALCULATE (
        [Avg_Point_per_user_per_Day],
        ALL ( MAIN[Date] ),
        FILTER (
            ALL ( MAIN[Rank_Date] ),
            MAIN[Rank_Date] >= vMinDateRank
                && MAIN[Rank_Date] <= vCurrentDateRank
        )
    )

RETURN
    vSumAvgPoint

It give me these values

[Harry_Tran]

@DataInsights 

For the above message.

The measure give me the values

[DataInsights]

@Harry_Tran,

Are you able to share your pbix? This will make it easier to troubleshoot.

[Harry_Tran]

@DataInsights 

https://drive.google.com/file/d/1AD6jvzXOmE68rG-Mp-SK_-7v1CAYobZd/view?usp=sharing 

The TopN_Rolling_7days works but the issue is I have a large amount of data so it is very slow.