cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DuncanMelville Frequent Visitor
Frequent Visitor

Creating a 24 month rolling forecast using prior 12 months actuals plus annual inflation

I am fairly new to PowerBI and need some direction on the best way to create a 24 month rolling forecast. I want to show the results of the calculated 24 months rolling forecast in one visualisation, and am unsure of the way to proceed.

In the Query Editor, I created two custom date columns to add 1 and 2 years to the actual transaction date.

In the Data View, I created two measures to calculate the year1 and year2 forecasts using the annual inflation from a separate table.

I now want to show the year 1 and year 2 calculated forecast in one visualisation and on one timeline. I don't want to have two separate visualisations. 

Should I create a calculated forecast table and push the year1 and year2 calculations into it? I would then be able to have one visualisation for the data.

I appreciate any advise on this.

Thanks, Duncan

1 ACCEPTED SOLUTION

Accepted Solutions
DuncanMelville Frequent Visitor
Frequent Visitor

Re: Creating a 24 month rolling forecast using prior 12 months actuals plus annual inflation

Thanks, Stachu. I found another way round this, but will need to come back to it in a while

2 REPLIES 2
Super User
Super User

Re: Creating a 24 month rolling forecast using prior 12 months actuals plus annual inflation

you don't need the additional columns for the additional years - just additional rows in your calendar table (see how to create one here: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/)

then you expose your forecast and sales using the measures and you should be able to do so in one visual, as they will all share the time axis

DuncanMelville Frequent Visitor
Frequent Visitor

Re: Creating a 24 month rolling forecast using prior 12 months actuals plus annual inflation

Thanks, Stachu. I found another way round this, but will need to come back to it in a while