Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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/
Solved! Go to 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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
118 | |
81 | |
48 | |
37 | |
27 |
User | Count |
---|---|
185 | |
73 | |
73 | |
50 | |
42 |