Forum Discussion
Help Needed: Historical Forecast Line Chart in Power BI
Hi everyone,
I’m trying to build a Power BI line chart that shows historical forecasts.
I need:
- 12 lines, each representing a forecast version from a different month (Forecast as of Jan, Feb, Mar, etc.)
- Each line should include Actuals for past months and Forecast for future months, ideally shown in two colors on the same line
- My forecast changes every month, so I want to keep and display the previous forecast snapshots, not only the latest forecast
What’s the best data model or DAX approach to achieve this?
Thanks in advance!
Hi Shinyegg ,
Power BI does not support splitting a line chart legend across multiple measures such as Actual, Forecast, and Budget. To address this, it's necessary to reshape the data so these measures are represented as rows instead of columns. In Power Query, unpivot the Actual, Forecast, and Budget columns to create a 'Scenario' column containing Actual, Forecast, and Budget and a single 'Amount' column. Then, define one measure to sum the Amount. For the visualization, place Month on the X-axis, the new measure in Values, and use either Snapshot Date for comparing forecast versions or Scenario for comparing Actual, Budget, and Forecast in the Legend. This approach aligns with Power BI’s data structure requirements and enables correct display of historical forecast lines.
6 Replies
- Ritaf1983
Super User
Hi Shinyegg
You are describing an architecture challenge rather than a DAX one. Power BI does not store historical snapshots natively. It reflects the current state of your source data upon refresh. If next month's forecast overrides this month's data in the source, the history is lost.
To achieve 12 different trend lines, you must "historicize" the data before loading it into Power BI. You need a backend process (like a SQL Stored Procedure, ETL script, or a Power Automate flow) that appends the current forecast to a history table at the end of every month.
Recommended "Snapshot Table" Structure: Your table should contain at least these fields to allow the visualization you described:
Snapshot_Date (Date): The date the forecast was generated (e.g., Jan 1st, Feb 1st). Drag this to the Legend.
Reference_Date (Date): The month being analyzed/forecasted (future or past). Drag this to the X-Axis.
Scenario (Text): "Actual" vs. "Forecast". Needed for your conditional coloring logic.
Value (Decimal): The amount.
Dimensions: Any other relevant columns (Product, Region, ID).
Once you have this structure, the DAX becomes simple, and the "12 lines" requirement is solved by using the Snapshot_Date field in the Legend bucket.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi ritaf1983,
I tried dragging the forecast date generated into legend and date ( month being analyze) but can't drag three measure: actual, forecast and budget into the value.
- Ashish_Mathur
Hi,
Not clear about your requirement. Share some data to work with and show the expected result.
