cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Average of a Measure for a period of time

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

1 ACCEPTED SOLUTION
Employee

``````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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

4 REPLIES 4
Community Champion

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``````

Super User

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))

Frequent Visitor

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?

Employee

``````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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors