Help Needed with DAX Expression for Current and Future Month Calculations
Hi everyone, I’m working on a DAX expression to calculate a display value for a line chart in Power BI. My goal is to correctly handle data for the current month and future months with the following requirements: For the current month: I need to combine the cumulative sum of actual values with the rolling wave forecast value. For future months: The display value should consist of the cumulative sum of the actual values from the current month plus the forecast value for that future month. Here's the DAX expression I have so far: Display Value = VAR IsCurrentMonth = YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY()) VAR IsFutureMonth = YEAR([Date]) > YEAR(TODAY()) || (YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) > MONTH(TODAY())) VAR CurrentMonthActualCumulativeSum = CALCULATE( MAX([Actual Cumulative.Sum]), FILTER( ALLSELECTED(), YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY()) ) ) RETURN IF( [Series Type] = "Rolling Wave Forecast", IF( IsCurrentMonth, [Actual+Rolling Wave Forecast], IF( IsFutureMonth, CurrentMonthActualCumulativeSum + [Value], [Actual+Rolling Wave Forecast] ) ), [Value] ) The Problem: When I add this measure to a line chart, the current month’s values display correctly. However, for future months, the cumulative sum is incorrectly added multiple times. For example, in the next month, the cumulative sum of the actual values from the current month is added again, and this continues to accumulate incorrectly in subsequent months. What I Need: I need the calculation to add the cumulative sum of the actual values only once for the current month and then add the forecast value for each future month without repeating the cumulative sum. Any guidance on how to fix this issue would be greatly appreciated! Thank you! (Images with captions are below)
