Running Total then Avg over a specified time period

[dpearson_soco]

I have a situation for accounting where they want to look at the Running totals for our AR and then compare what the Average was for the last 13 months for trending. I can get to the running totals just fine, but cannot figure out how to then average those running totals for the last 13 months:

Here is what I have so far:

DSO_AvgAR =

VAR LastCurrentDate = MAX ( 'd_Date'[CalendarDate] )

VAR Period13 = DATESINPERIOD ( 'd_Date'[CalendarDate], LastCurrentDate, - 13, MONTH )

VAR RunningAR = CALCULATE(SUMX(f_GeneralLedger,f_GeneralLedger[Amount]),d_Date[CalendarDate] <= LastCurrentDate, f_GeneralLedger[AccountNumber] in {"10225","10226","10250","10275","10300","10325","10350","10380","10381","10382","10383","10384","10385"})

Var AVG_AR = CALCULATE(AVERAGEX(VALUES ('d_Date'[YearMonth]), RunningAR), Period13)

RETURN AVG_AR

Here is some sample data of what the RunningAR looks like for the last 13 months:

Month	AR Value
1	$339,022
2	$487,064
3	$386,998
4	$388,119
5	$432,743
6	$434,630
7	$413,986
8	$483,239
9	$439,252
10	$563,513
11	$696,451
12	$454,170
13	$388,795

The Average should be $454,460.18

[johnt75 · Accepted solution]

The problem is the RunningAR variable. Variables are only calculated once, so it is not being recalculated within the AVERAGEX, each month will use the same value. Either move the code for RunningAR inside the AVERAGEX or use that code to define a measure, and call the measure from within the AVERAGEX.