Averaging a measure over 3 Months - strange results

DAX DAX Calculations
1

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
) 
VAR basetable = ADDCOLUMNS(
    SUMMARIZE(
        DateFilterFact,
        Reports[RepKey],
        Reports[repLinedesc],
      'Calendar'[Fisc MONTH Year],
        Companies[BusinessUnit]
    ),
    "Last3 YTD", [YTD Act]
) 
VAR BSheetHistory = CALCULATETABLE(
    SUMMARIZE(
        basetable,
        [RepKey],
        [RepLineDesc],
        [Fisc MONTH Year],
        [BusinessUnit],
        [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
2

Hi @BINavPete

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

3

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

Did the response from @ankit help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hi @BINavPete, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.

5

HI @ankit
Apologies for not responding earlier. This does appear to be working fine.

Thanks for your help
Pete