# Averaging a measure over 3 Months - strange results

HI guys
I am trying to compute an average Balance sheet amount over last 3 months.
The Fact table holds the opening amount and monthly changes. So to arrive at the current month Balance sheet amount a simple YTD formula applies. To get an average we need the last 3 months YTD amounts divide by 3. Hence the challenge of averaging a measure!

The DAX below, filters the fact table for the last 3 months and builds a calculated table, which in theory with a bit of SUMX/AverageX will find the value I want. When I perform this task in DAX Studio, I am getting the correct virtual table and with some filtering to apply some level of context I can see the required amounts.

``````3m Avg =
VAR thisoffset = -3
VAR DateFilterFact = Filter(
All( MAFact ),
RELATED( 'Calendar'[AccountingPeriodOffset] ) >= thisoffset + 1 &&
RELATED( 'Calendar'[AccountingPeriodOffset] ) <= 0
)
SUMMARIZE(
DateFilterFact,
Reports[RepKey],
Reports[repLinedesc],
'Calendar'[Fisc MONTH Year],
),
"Last3 YTD", [YTD Act]
)
VAR BSheetHistory = CALCULATETABLE(
SUMMARIZE(
basetable,
[RepKey],
[RepLineDesc],
[Fisc MONTH Year],
[Last3 YTD]
)
) Return If(
HASONEVALUE( Reports[RepKey] ),
DIVIDE(
SUMX( BSheetHistory, [Last3 YTD] ),
thisoffset * -1,
blank()
),
0
)
``````

We are using both the ProfitBase Financial reporting Matrix and the Standard Matrix in our PBIX and both give different results.

The ProfitBase matrix gives:

• Correct results for the Business Units (2nd level of drill)

• Correct totals when all business units selected

• Incorrect Divisional totals when one division selected

• A total which doesnâ€™t mean anything when one business unit is selected

The Microsoft Matrix doesnâ€™t produce any meaningful numbers at all!!

Iâ€™ve never had any issues with the Profitbase matrix reporting differently than the microsoft one, and I guess if we can get the microsoft one working the Profitbase one will take care of itself. At least the Profitbase one gives a reference of what weâ€™re trying to achieve.

The PBIX uploaded is all test data. I did try to shrink it down to just the problem in hand but that proved a little elusive for me. Apologies.
Averages.pbix (2.8 MB)

Looking forward to help

Pete
PS; Hey @BrianJ. Whilst DAX might be tricky, I have mastered formatting my code in a post now!!

1 Like

Have made changes to your measure as earlier it seems to be removing context for other columns also.

Can check the new measure â€ś3m Avg_Ankitâ€ť in attached file
Averages.pbix (2.8 MB). Change is till DateFilterFact only, rest all is same.

``````3m Avg_Ankit =

Var thisoffset = -3

var MinDate = CALCULATE(min('Calendar'[Date]),all('Calendar'),'Calendar'[AccountingPeriodOffset] = thisoffset + 1)

var MaxDate = CALCULATE(MAX('Calendar'[Date]),all('Calendar'),'Calendar'[AccountingPeriodOffset] = 0)

//Var DateFilter = Filter(All('Calendar'),'Calendar'[AccountingPeriodOffset]>= thisoffset + 1 && 'Calendar'[AccountingPeriodOffset]<=0)

Var DateFilterFact = CALCULATETABLE(MAFact,MAFact[PostingDate] >= MinDate && MAFact[PostingDate] <= MaxDate,all('Calendar'))
``````

Thanks
Ankit J

Hello @BINavPete, good to see that you are having progress with your inquiry.