cancel
Showing results for
Did you mean:
Highlighted
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.

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

## 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```

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

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```

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

Announcements

#### 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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 284 members 3,229 guests
Recent signins: