Average of a Measure for a period of time

[tgryder]

I have a measure called "First30Days" that goes back for 30 days of the Daily Sales and summarizes 30 days of sales for each calendar day.  (I know the dataset is not every calendar day, that is ok.)

First30Days = CALCULATE(('SalesTable'[DailySales]),DATESINPERIOD('Date Dim'[Date],NEXTDAY('Date Dim'[Date])-30,30,DAY))

As the screenshot indicates, this works fine and as expected.

What I can't seem to figure out is how to average the First30Days column over 30 calendar days.

I should end up having a new measure that shows me the 30 day average of the First30Days.

Based on the screenshot, i should get 647,547 in my new column on the last row. (And, I will get a result in each row, but I am only showing for that one day in this example.)

Year       Quarter Month                                First 30 Days      AVERAGE OF First 30 Days

2020      Qtr 2     June       22                         $492,785            

2020      Qtr 2     June       23                         $515,496            

2020      Qtr 2     June       24                         $563,194            

2020      Qtr 2     June       25                         $589,350            

2020      Qtr 2     June       26                         $581,721            

2020      Qtr 2     June       29                         $612,746            

2020      Qtr 2     June       30                         $647,252            

2020      Qtr 3     July        1                            $660,788            

2020      Qtr 3     July        2                            $669,226            

2020      Qtr 3     July        3                            $629,239            

2020      Qtr 3     July        6                            $649,151            

2020      Qtr 3     July        7                            $680,475            

2020      Qtr 3     July        8                            $678,310            

2020      Qtr 3     July        9                            $679,352            

2020      Qtr 3     July        10                         $671,994            

2020      Qtr 3     July        13                         $678,355            

2020      Qtr 3     July        14                         $718,676            

2020      Qtr 3     July        15                         $732,290            

2020      Qtr 3     July        16                         $733,909            

2020      Qtr 3     July        17                         $704,970            

2020      Qtr 3     July        20                         $709,198             647,547

[AntrikshSharma]

Try this:

Rolling Average =
VAR TimeFrame = 30
VAR LastVisibleDate =
    MAX ( Dates[Date] )
VAR DatesWithinLastNDays =
    FILTER (
        ALL ( Dates ),
        Dates[Date] < LastVisibleDate
            && Dates[Date] >= LastVisibleDate - TimeFrame
    )
VAR Result =
    CALCULATE ( [Average Sales], DatesWithinLastNDays )
RETURN
    Result

[amitchandak]

@tgryder ,

Try a measure like

measure =
var _min = minx(all(Table),Table[Date])
var _max = _min+30 // or 29 as per need
return 
calculate(Average(Table[price]), filter(all(Table), Table[Date]>=_min && Table[Date]<=_max))

[tgryder]

               I tried the formula but in the last line:  “calculate(Average(Table[price]), filter…….”

                 I put in        calculate(Average(‘Sales’]), filter…..

And I get this error message:

               Column ‘DailySales’ in table ‘Sales’ cannot be found or may not be used in this expression

Do you know why that error would appear?

[mahoneypat · Accepted solution]

Please try this expression in your measure

Avg 30 Days =
VAR thisdate =
    SELECTEDVALUE ( Date[Date] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( Date[Date] ), [First30Days] ),
        ALL ( Date[Date] ),
        Date[Date] <= thisdate,
        Date[Date] >= thisdate - 29
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat