Help Needed with DAX Expression for Current and Future Month Calculations

Hi everyone,

Thank you for your previous assistance. I need to provide additional context regarding my issue.

I'm using the following DAX expression to display data on the y-axis:  

Cumulative Spend NEW =
CALCULATE(
SUM('Budget/Spend by Category'[Display Value2]),
FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date]))
)

• Data Structure:
  
  • I have a table where each row represents a budget or spend entry, and the granularity is one row per series type per month.
  • The column [Series Type] contains different categories, including "Actual" and "Rolling Wave Forecast".
  • [Actual Cumulative.Sum] represents a cumulative sum of actual values up to the current month.

• Objective:
  
  • I need to calculate a display value (Display Value) that correctly combines actuals and forecasts for the current and future months.
  • Specifically, for the current month, I need to add the actual cumulative sum to the rolling wave forecast.
  • For future months, I want to display the cumulative actual sum for the current month added to each month's rolling wave forecast.
     
• Current Problem
  • When I plot Cumulative Spend NEW on a line chart, the values for "Rolling Wave Forecast" are significantly higher than expected. It appears that the cumulative sum is being over-counted, possibly due to the way Display Value2 is being calculated or aggregated.

• Additional Observations:
  
  • The values for the current month seem accurate.
• In future months, the "Rolling Wave Forecast" values appear inflated, potentially due to repeated addition of the cumulative actual sum.I would appreciate any insights or suggestions on how to correct this calculation to ensure an accurate representation of the data on the line chart.
  
  Thank you for your help!

Anonymous

bhanu_gautam