# Moving Average of a Ratio Measure in DAX

• ### Question

• Usually I can figure things out with DAX if I have enough late nights...but this one has me stumped...and it seems easy.

My table is as below:

 Date Flag Command Account 20140401 -1 1,242,404 213 20140401 0 30,661,903 69,717 20140402 -1 1,258,405 235 20140402 0 29,793,906 70,163 20140403 -1 1,292,929 239 20140403 0 29,204,737 73,205 20140404 -1 1,119,171 253 20140404 0 28,113,396 69,049 20140405 -1 648,214 147 20140405 0 11,646,215 38,447 20140406 -1 561,727 136

I have a measure that gives me the correct results which is a percentage of commands per account:

Measure1=sum([Command])/SUM([Accounts])

I include this measure into my moving average measure to get the 7 day average of commands per account:

MA Measure=CALCULATE([Measure1],DATESINPERIOD(DimDate[FullDateAlternateKey], LASTDATE(DimDate[FullDateAlternateKey]),-7,DAY))

The results of the moving average measure seem to be summing 7 days of the COMMAND column and then summing 7 days of the ACCOUNT column and then dividing for a day. What I want is to divide the COMMAND and ACCOUNT column for each day and then find the average for a 7 day period.

Hopefully I have explained it well enough....like I said, it seems simple but I am not making any progress.

Thanks in advanced and let me know!

Tuesday, October 21, 2014 9:15 PM

• Your CALCULATE() is simply manipulating the context that the measure is evaluated in, not the order of evaluation; you're saying take the rolling 7 day period, and then after having returned all data in that context, take the sum of [Command] across all of that, then divide that by the sum of [Accounts].

What you're looking for is AVERAGEX() which will perform an expression for each row in a table (passed as an argument) and then averages the results.

```MA Measure:=
AVERAGEX(
DATESINPERIOD(
DimDate[FullDate]
, LASTDATE( DimDate[FullDate] )
, -7
, DAY
)
, [Measure1]
)```

The table is DATESINPERIOD(), which gives us a table of seven dates. For each row in that table, [Measure1] is evaluated, giving us 7 values. Those 7 values are summed and then divided by 7.

• Marked as answer by Tuesday, October 21, 2014 10:38 PM
Tuesday, October 21, 2014 10:31 PM

### All replies

• Your CALCULATE() is simply manipulating the context that the measure is evaluated in, not the order of evaluation; you're saying take the rolling 7 day period, and then after having returned all data in that context, take the sum of [Command] across all of that, then divide that by the sum of [Accounts].

What you're looking for is AVERAGEX() which will perform an expression for each row in a table (passed as an argument) and then averages the results.

```MA Measure:=
AVERAGEX(
DATESINPERIOD(
DimDate[FullDate]
, LASTDATE( DimDate[FullDate] )
, -7
, DAY
)
, [Measure1]
)```

The table is DATESINPERIOD(), which gives us a table of seven dates. For each row in that table, [Measure1] is evaluated, giving us 7 values. Those 7 values are summed and then divided by 7.

• Marked as answer by Tuesday, October 21, 2014 10:38 PM
Tuesday, October 21, 2014 10:31 PM
• Yes!

So simple...I knew it would be :)

Thanks Greg!

Tuesday, October 21, 2014 10:38 PM