Forum Discussion
Help Needed: Historical Forecast Line Chart in Power BI
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.
Super UserHi 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.
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.Hi Shinyegg ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you
Hi Shinyegg ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
