Need help creating a measure

[Shaki]

Hi,

I have a table with information about taxi trips in NY between 2017-2020.

I created a measure to forecast trips in 2021 (Assuimng the Pandemic is still impacting and assuming that the pandemic is not impacting):

=VAR _trips2017 =
CALCULATE( [Trips], '454_calendar'[FiscalYear] = 2017 )
VAR _trips2018 =
CALCULATE( [Trips], '454_calendar'[FiscalYear] = 2018 )
VAR _trips2019 =
CALCULATE( [Trips], '454_calendar'[FiscalYear] = 2019 )
VAR _trips2020 =
CALCULATE( [Trips], '454_calendar'[FiscalYear] = 2020 )
VAR _diff18 =
DIVIDE( _trips2018 - _trips2017, _trips2017 )
VAR _diff19 =
DIVIDE( _trips2019 - _trips2018, _trips2018 )
VAR _diff20 =
DIVIDE( _trips2020 - _trips2019, _trips2019 )
VAR _nopan =
DIVIDE( _diff18 + _diff19, 2 )
VAR _withpan =
DIVIDE( _diff18 + _diff19 + _diff20, 3 )
VAR _est = _trips2020 + ( _trips2020 * _withpan )
RETURN
_est

1. If i want to had a calculation of 95% confidance level range for each of them, do i need to creat 2 more meaures? or is there a better and more simple way to do it? 

2. What is the best way to creat a measure to forecast the trips in 2022? should i use the same slope that i used for forecasting 2021 trips? Is there another way to calculate the new slope with the number of trips the I now have from forecasting 2021 trips?

Thanks in advance(:

[ValtteriN · Accepted solution]

1. I would just create more measures. Making your existing measure more complicated has its own issues, since that might invite performance problems and it will make understanding the measure more complicated for people other than yourself (assuming you have to explain the calculation logic to someone else).

2. The simplest way is to use the same slope. If you indeed have data from 2021 now you can and maybe should use that. 

3. Just as a general note you should consider using LY calculation when calculating the differences. Additionally perhaps you could use a table/another visual and use SELECTEDVALUE/MAX for getting the year.

With these in mind an alternative calculation could be something like:

VAR _year =
MAX('454_calendar'[FiscalYear])
VAR _trips =
CALCULATE( [Trips], '454_calendar'[FiscalYear] = _year )
VAR _LYtrips =
CALCULATE(_trips,SAMEPERIODLASTYEAR('calendar'[date]))
VAR _diff =
DIVIDE( _trips - _LYtrips, _LYtrips)
VAR _LYdiff =
CALCULATE(_diff,SAMEPERIODLASTYEAR('calendar'[date]))


VAR _nopan =
DIVIDE( _diff + _LYdiff, 2 )
...


So what I am suggesting is to make your DAX more re-useable. Since you are using LY calculations and difference calculations a lot here I would also consider making calculation groups to make these calculations and utilizing these. For now this is all that comes to my mind and to modify the calculation logic further I would need to see some sample data and desired end result out of this data.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!