Creating DAX to predict future data - forecasting based on another measure

Question

I am trying to create a measure to predict current year and future year's data on a trend chart. Like below, I have unites and sales records in one table. I created these two charts by applying a filter, which shows if the record is sales or unit. However, I only have sales forecast data for 2021 and 2022. Using sales YoY changes, I want to create a DAX for unit prediction.&nbsp;Here is the logic I want to create, and anyone know the correct syntax?&nbsp;If year(FY First Day) &gt;= year(dateadd(today(),+6,'month')), then previous year's unit*sales YoY change.&nbsp;&nbsp;For example, in this chart, I want to calculate the sales changes from 2021 to 2022. Using this fraction as a factor to multiply the unit in 2021 to get 2022's prediction.&nbsp;&nbsp;&nbsp;Below are the dataset...FY First Day&nbsp; Amt&nbsp;Category2019-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123&nbsp;unit A2019-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2,341&nbsp;unit B2019-01-01 0:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,323&nbsp;sales2020-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1,234&nbsp;unit A2020-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200&nbsp;unit B2020-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 234&nbsp;sales2020-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2,543&nbsp;sales2021-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 124&nbsp;unit A2021-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200&nbsp;unit B2021-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123&nbsp;sales2021-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3,456&nbsp;sales2022-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp;unit A2022-01-01 0:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5400&nbsp;sales&nbsp;&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
In the Table that you have shown, please show the expected result in another column.

icey · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Please check if this post could help you:&nbsp;Solved: Running Increase of Forecast based on Actuals - Microsoft Power BI Community.
&nbsp;
It calculated forecast vlaues based on actual values.
&nbsp;
&nbsp;
Best Regards,
Icey
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Creating DAX to predict future data - forecasting based on another measure

2 Replies

Related content

HR - Predictive / Forecast Analysis

Predictive analytics solution

Forecasting with DAX

Forecasting

Predictive Forecasting Tool

FY First Day	Amt	Category
2019-01-01 0:00	123	unit A
2019-01-01 0:00	2,341	unit B
2019-01-01 0:00	2,323	sales
2020-01-01 0:00	1,234	unit A
2020-01-01 0:00	200	unit B
2020-01-01 0:00	234	sales
2020-01-01 0:00	2,543	sales
2021-01-01 0:00	124	unit A
2021-01-01 0:00	200	unit B
2021-01-01 0:00	123	sales
2021-01-01 0:00	3,456	sales
2022-01-01 0:00	20	unit A
2022-01-01 0:00	5400	sales