topic Help Needed with DAX Expression for Current and Future Month Calculations in DAX Commands and Tips

Help Needed with DAX Expression for Current and Future Month Calculations

Jules102 — Tue, 23 Jul 2024 20:41:06 GMT

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)

Re: Help Needed with DAX Expression for Current and Future Month Calculations

bhanu_gautam — Wed, 24 Jul 2024 06:10:31 GMT

Jules102 , First I would like to say good work in explaining the issue , please find the updated measure try using it once

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('YourTableName'),
YEAR([Date]) = YEAR(TODAY()) &&
MONTH([Date]) = MONTH(TODAY())
)
)

VAR FutureMonthForecastValue =
CALCULATE(
SUM([Value]),
FILTER(
ALLSELECTED('YourTableName'),
[Series Type] = "Rolling Wave Forecast" &&
[Date] = EARLIER([Date])
)
)

RETURN
IF(
[Series Type] = "Rolling Wave Forecast",
IF(
IsCurrentMonth,
[Actual+Rolling Wave Forecast],
IF(
IsFutureMonth,
CurrentMonthActualCumulativeSum + FutureMonthForecastValue,
[Actual+Rolling Wave Forecast]
)
),
[Value]
)

Re: Help Needed with DAX Expression for Current and Future Month Calculations

Anonymous — Wed, 24 Jul 2024 06:30:43 GMT

Hi Jules102

I tried to reproduce your situation, but I found that I didn't have your specific data

I've also double-checked the dax you offered, according to your logic, there is no problem with it.

So I speculate that maybe the [Value] is the problem.

If possible, could you please provide some raw data(exclude sensitive data), or create some sample data, so that we can help you better.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Help Needed with DAX Expression for Current and Future Month Calculations

Jules102 — Wed, 24 Jul 2024 14:16:43 GMT

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