Adding rows for actual finance-data for pre forecast periods

[Anders1]

I have this dataset with financial data. I have actual data first 3 periods of the year, and i have a few forecasts, one with data from period 2 and one with data from period 3. But the forecasts do not have data for months that have passed. For the forecast data i want to add rows of actual data in the periods that have no forecastdata. Prefer to add this data in power-query, so that when the table is loaded i can filter on one of the forecast, and it will show actual data for the periods that have no forecastdata. If there is a better solution to create a DAX for this, it may be an alternative, but my other tables in the dataset is build up like this. The table will be populated with new forecasts each period in the future. 

Year	Periodnr	Plan	Amount
2025	1	Actuals	10
2025	2	Actuals	20
2025	3	Actuals	30
2025	2	Forecast 1	30
2025	3	Forecast 1	40
2025	4	Forecast 1	50
2025	5	Forecast 1	50
2025	6	Forecast 1	60
2025	7	Forecast 1	70
2025	8	Forecast 1	80
2025	9	Forecast 1	90
2025	10	Forecast 1	90
2025	11	Forecast 1	80
2025	12	Forecast 1	100
2025	3	Forecast 2	40
2025	4	Forecast 2	50
2025	5	Forecast 2	50
2025	6	Forecast 2	60
2025	7	Forecast 2	70
2025	8	Forecast 2	80
2025	9	Forecast 2	90
2025	10	Forecast 2	90
2025	11	Forecast 2	80
2025	12	Forecast 2	100

Preffered result. This excample is when filtering on forecast 1. The text marked in green color are actual numbers but, but in report apears to be a part of forecast-data.

Year	Periodnr	Plan	Amount
2025	1	Forecast 1	10
2025	2	Forecast 1	30
2025	3	Forecast 1	40
2025	4	Forecast 1	50
2025	5	Forecast 1	50
2025	6	Forecast 1	60
2025	7	Forecast 1	70
2025	8	Forecast 1	80
2025	9	Forecast 1	90
2025	10	Forecast 1	90
2025	11	Forecast 1	80
2025	12	Forecast 1	100

[v-pnaroju-msft]

Hi Anders1,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi Anders1,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Thank you, @Ant_Fri, for your response.

Hi Anders1,

We would like to check if the solution provided by @Ant_Fri has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[Ant_Fri]

Hi @Anders1 

I suggest the following steps :

Duplicate your table as per follows : One duplicate table is “ Actuals  “ , one is “ Forecast  “ and another is “ Forecast 2  “

In Forecast :

-Filter out Actuals

-Add a custom column named Joinkey and insert a 1

-Remove all columns except Plan and Joinkey

-In Plan column remove duplicates

In Forecast 2 :

-Filter out Actuals

In Actuals:

-Filter out Forecast

-Add a custom column named Joinkey and insert a 1

New Table : ActualwithForecast

-Merge Actuals to Forecast ( on joinkey > inner join ) to create a table ActualwithForecast

-Expand table

-Keep columns Year, Period,  Amount and Forecast.Plan ( last colum here is from the table you just joined )

-Rename Forecast.Plan to Plan

New Table : Merged 2

-Merge ActualwithForecast to Forecast 2   (  in the merge select Year , Period, Plan for both tables ) and select  Left Anti as join kind

-Remove the Expand Table

Append  Forecast2 to Merged 2 and you should have the below table which is similar to your preferred result :

This is the data under FinancialData :

Hope this helps

Antonio

[Anders1]

Thank you for your suggestion, and this would work, but is not a good aproach since my dataset will get 1 new forecast each month, and i then need to manually add one more merge table for each month. And after one eyear i will have 12 of these tables. Maybe a DAX query would be the only answer here?

[Ant_Fri · Accepted solution]

Hi @Anders1 

Power Query will dynamically consolidate all forecasts into a single query with the Plan column . So new forecasts are automatically added with no manual steps or extra tables.

You would automatically have an extra forecast 3 as per below results :

Financial Data with forecast 3 added :

If still an issue ( as I am guessing what your extra forecast looks like )  I suggest send some extra screenshots and further clarifications of what the problem is. I can then have another look.

Antonio

[lbendlin]

how do you get from 10 to 110 ?