cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tgryder
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.

 

Average.jpg

 

 

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
mahoneypat
Microsoft
Microsoft

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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
AntrikshSharma
Community Champion
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

 

amitchandak
Super User
Super User

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

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

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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors