topic Re: Time Projection - 5 Years in DAX Commands and Tips

Time Projection - 5 Years

piotrgrendus87 — Fri, 19 Aug 2022 13:46:21 GMT

Hello Everyone.
I would like to prepare measures that will allow me to create so-called projections for the future based on historical data. Currently I have:
- a base month from which I want to start calculations ( 2022-01 )
- the current indicator, which is updated daily
- the % growth rate, which represents whether the trend is upward or not, to calculate this I used the following measure:

Growth Rate % = DIVIDE(Capacity_FTE_Total[Capacity_FTE], HIST_CAPACITY[History FTE]) - 1

Now I would like to create a measure that, based on the change that occurred between the base month and the current rate, will predict what " Capacity will have in 2023-01. Based on the result of 2023-01, it will predict what the result ratio will be in the following year, etc. etc. ( forecast up to a maximum of 5 years ).

Is it possible to create this type of measures in Dax ?

Regards Piotr.

Re: Time Projection - 5 Years

lbendlin — Sun, 21 Aug 2022 00:02:48 GMT

Sounds like you are looking for PRODUCTX ?

Re: Time Projection - 5 Years

piotrgrendus87 — Mon, 22 Aug 2022 07:50:20 GMT

Hi.
I think that maybe be a solutions for me.
Could you help me to create the code that will calculate this, based on my previous calculations.

Regards Piotr

Re: Time Projection - 5 Years

lbendlin — Mon, 22 Aug 2022 11:22:07 GMT

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Time Projection - 5 Years

piotrgrendus87 — Thu, 25 Aug 2022 06:51:06 GMT

Hello, below is a sample of the data

Start Point ( January 2022 )	Current Point ( August 2022)	Growth Rate %	January 2023	January 2024	January 2025	January 2026
1952	2006	2,77%	2011	2040	2050	2060
259.5	256.5	-1,16%	250	260	270	285
348	406	16,67%	410	420	450	470

First 3 columns - Calculation based on the data

Last 4 columns - Manually sample data as expected result

I want to create a calculation that based on the value of " Growth Rate % " will calculate my probable result in 2023, while based on the value of 2023 will calculate my result for 2024, and so on and so forth. The only value that will change daily is " Current Point ", since it will be updated daily and its change should also be reflected for the values for 2023, 2024, 2025, 2026. " Start Point ", will be set once a year and will not change during the 12 months. I don't know if my approach to the topic is correct or not ?, maybe it will just be simpler to use the built-in forecasting model that is in PowerBi.

Regards Piotr

Re: Time Projection - 5 Years

lbendlin — Sat, 27 Aug 2022 21:33:45 GMT

See attached for a pedestrian implementation. It includes a calendar table even though that is not strictly required.