Dax for LTM rolling 12 month from an average expression

[checsin]

Hi,

I need to use DAX to calculate the LTM value in 2 steps as illustrated below in the excel. 

Step-1: the monthly value is calculate by an average value of last two months.

AC15 = (AB12 + AC12) / 2

AB15 = (AA12 + AB12) / 2

...

Step-2: passing these average values as an input of each month into a sum of rolling 12 months. And then make an average value of the result from the sum of rolling 12 months.

AC18 = average(R15:AC15)

The actual table is not like this excel. There are one column [Date] and another column [ActivePortfolio].

I use this DAX formula for step-1. This gives me correct result.

[MonthlyValue] := CALCULATE (SUM([ActivePortfolio]), DATESINPERIOD (row5, LASTDATE( [Date] ), -2, MONTH)) / 2

But if I use the following DAX formula in step-2, it returns a very small value for that month (AC18).

[LTMValue] := CALCULATE ([MonthlyValue], DATESINPERIOD (row5, LASTDATE( [Date] ), -12, MONTH)) / 12

So what's wrong in the DAX [LTMValue]?

[Anonymous · Accepted solution]

If  [MonthlyValue] is calculated correctly, then [LTMValue] is THE AVERAGE of [MonthlyValue] over the period of 12 months, not just CALCULATE( [MonthlyValue], ...). You have to do two things:

1. You have to check if the level selected is MONTH, that is, if the filtered value is a full month.
2. Once this is true, you just get the average of [MonthlyValue] over the period of last 12 months.

By the way, you have to have a 'Date' table that joins to your Portfolios and which is marked as DATE TABLE in the model and covers full years of the dates found in Portfolios[Date].

var __lastVisibleDate = LASTDATE( 'Date'[Date] )
var __startDate = PREVIOUSYEAR( __lastVisibleDate ) + 1
var __months =
    CALCULATETABLE(
        VALUES( 'Date'[Month] ), -- Month must be unique across years
        DATESBETWEEN(
            'Date'[Date],
            __startDate,
            __lastVisibleDate
        )
    )
var __average =
    CALCULATE(
        AVERAGEX(
            __months,
            [MonthlyValue]
        ),
        ALL( 'Date' )
    )
return
    __average

If the last visible date in your selections is the last date of a month, this will work correctly