topic Re: Average of a Measure for a period of time in DAX Commands and Tips

Average of a Measure for a period of time

tgryder — Tue, 21 Jul 2020 21:48:51 GMT

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

Re: Average of a Measure for a period of time

mahoneypat — Tue, 21 Jul 2020 23:36:03 GMT

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

Re: Average of a Measure for a period of time

amitchandak — Wed, 22 Jul 2020 01:59:11 GMT

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

Re: Average of a Measure for a period of time

AntrikshSharma — Wed, 22 Jul 2020 04:19:20 GMT

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

Re: Average of a Measure for a period of time

tgryder — Wed, 22 Jul 2020 14:50:36 GMT

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?