cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Average of 6 month and 12 month future trend

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.

TrendAverage.jpg

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/

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Average of 6 month and 12 month future trend

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

TrendAverage2.jpg

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

1 REPLY 1
Super User
Super User

Re: Average of 6 month and 12 month future trend

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

TrendAverage2.jpg

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 284 members 3,229 guests
Please welcome our newest community members: