Average of 6 month and 12 month future trend

[jdbuchanan71]

I have a model that calculates then trend using the simple linear regression and the uses that trend to display a projection.  A copy of the model sample is here:  https://www.dropbox.com/s/6f0juje5qmbpvns/Sample%20Data.pbix?dl=0

I am trying to calculate the 6 month and 12 month average projection based on the last 12 months actual trend but can't quite get it to work.  It seems like any filtering I do of the date table throws off the trend because it needs the range to calc the trend.

The amounts I am trying to calc are below in red and blue.

The ultimate goal is to be able to display ([6 month avg trend] * [member count on last month] * 6) to estimate the spend for the next 6 months.  The measure [_PMPM Projection Total] is my attempts at the 6 month average calc.  All the measures are in the Measure Table.

Anyone have thoughts on this?

*Thanks to Daniil Maslyuk for his post about the linear regression.  https://xxlbi.com/blog/simple-linear-regression-in-dax/

[jdbuchanan71 · Accepted solution]

I was able to get this to work using CONCATENATEX to turn the last two trend values into a string then going from there and splitting the string up to calc the estimated 6 mo and 12 mo spend.  Conversion to a string seemed to protect the calcs from strageness around dates.

PMPM Trend Values for Estimate =
VAR LastPaidDate = CALCULATE ( MAX ( vCLAIM[Paid Date] ), ALL ( DATES ) )
VAR ProjectionStart = DATE ( YEAR ( LastPaidDate ), MONTH ( LastPaidDate ) - 1, 1 )
VAR TrendedMonths = 
    CALCULATETABLE (
        VALUES ( DATES[Month Year] ),
        DATESBETWEEN ( DATES[Date], ProjectionStart, LastPaidDate )
    )
RETURN
    CALCULATE (
        CONCATENATEX (
            TrendedMonths,
            FORMAT ( [Paid Amount PMPM Trend], "00000.00000" ),
            "|"
        ),
        ALLSELECTED ( DATES )
    )

6 MO Projected = 
VAR ProjectionMonths = 6
VAR StartTrend = VALUE ( LEFT ( [PMPM Trend Values for Estimate], 11 ) )
VAR EndTrend = VALUE ( RIGHT ( [PMPM Trend Values for Estimate], 11 ) )
VAR MonthTrendAmount = EndTrend - StartTrend
VAR FirstProjection = EndTrend + MonthTrendAmount
VAR LastProjection = FirstProjection + ( MonthTrendAmount * ( ProjectionMonths - 1 ) )
VAR AvgProjection = ( FirstProjection + LastProjection ) / 2
VAR CurrentMembers = CALCULATE ( [Member Count from Enrollment], ALL ( DATES[Date] ), DATES[Date] = TODAY() )
VAR Result = AvgProjection * CurrentMembers * ProjectionMonths
RETURN Result

Possibly the ugliest thing I have written that still worked but it did work.