Create forecast table

[Syndicate_Admin]

Best regards to the attentive community.

I am looking for an alternative to establish a Forecast that consists simply of increasing the figures in the table by a percentage factor. For this I require a table with modified dates (Year + 1) in order to be able to relate this table to the Calendar and show the successive years with such a specific increment.

My idea was to generate a table that repeated the same data 4 times but with different value in the column of [Year] (the 4 successive years) to possibly indicate the increment factor and do the multiplication row by row throughout the table. Unfortunately I was not able to set this procedure in DAX, maybe it is not optimal but in Query I do not know most of the functionalities.

I hope you have been clear with my consultation and you can support me.

Nice day.

[Ashish_Mathur]

Hi,

As an alternative, you may ensure that your Calendar Table goes till the current year + 4.  This calculated Table will build a Calendar till 4 years from the current month

Calendar = calendar(min(Data[Date]),edate(eomonth(today(),0),48))

Hope this helps.

[Syndicate_Admin]

I appreciate the prompt reply.

I did not know the GENERATE function, it was of great help to expand my table and determine the new date from the VALUE generated by the GENERATESERIES function.

My drawback is now to generate the figures with an iterative year-by-year increment, since the percentages do not accumulate. But I have already come a long way thanks to your contribution.

Best regards.

[amitchandak]

@Syndicate_Admin , if you need rows you can do crossjoin or generate with year

example

generate([Table]. generateseries(2020,2024,1) )

you can addcolumns and add some logic

addcolumns(generate([Table]. generateseries(2020,2024,1) ),

"New value" , Switch(true(),

[year] =[value], [Amount],

[year] < [value], [Amount],[value], [Amount] * (1+([Value] - [Year])/100)

)

here value us coming because of generateseries