Forecast Line graph without going over 100%

[Anonymous]

I currently have a quarterly success measure that ranges from 0 - 100 and I would like to predict the next values over 1 / 2 upcoming quarters. I know I can use the [analytics] - [foreasting] section to predict upcoming values within 95% / 99% confidence interval. However as I have a lot of max values = 1 / 100%  some of the predictions are over 100 which isn't helpful for what I need.

I have written a DAX code for Success and a comparison to Last Year:

Success = DIVIDE([Sales],[Estimated Sales])

Success Last Year = CALCULATE(DIVIDE([Sales],[Estimated Sales]),DATEADD('DimDate'[Dates],-4,QUARTER))

Quarter Year	Success	Success Last Year
Q1 2019	1.00
Q2 2019	0.52
Q3 2019	0.45
Q4 2019	1.00
Q1 2020	0.54	1.00
Q2 2020	0.46	0.52
Q3 2020	0.60	0.45
Q4 2020	1.00	1.00
Q1 2021	0.56	0.54
Q2 2021	0.13	0.46
Q3 2021	0.28	0.60
Q4 2021	0.28	1.00
Q1 2022		0.56
Q2 2022		0.13
Q3 2022		0.28
Q4 2022		0.28

But I feel this is too simplified to just compare last years values to this year.

Is there a way to forecast [Success] measure via DAX for the next Quarter or two without it going over 100?

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ,

I think you could  average last year's success and this year's success rate to get a prediction. The more years there are, the more reliable the data are. The DAX can be modified to the following form：

Success Last Year = (CALCULATE(DIVIDE([Sales],[Estimated Sales]),DATEADD('DimDate'[Dates],-4,QUARTER))+CALCULATE(DIVIDE([Sales],[Estimated Sales]),DATEADD('DimDate'[Dates],-8,QUARTER)))/2

I hope this idea can help you. If not, please provide me with more details about your problem and  logic or share me with your pbix file after removing sensitive data.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.