How to create a cumulative sum over two columns with comlementary Date Range in Power BI

2
votes

I'm trying to build a line Chart to show the cumulative sum of the Forcast. I have two Tables, one for the Actuals and one for the forcast, both linked to a Date table. The Chart should show the cumulative sum of the actuals till current month and from next month on those from the Forcast. I've created so far the following measures to get the Forcast Chart:

Chart_Forecast not cumulated = 
VAR Actual_Hrs_not_cumulated = CALCULATE([Total Actuals],FILTER(IN_ACTUALS, IN_ACTUALS[Date] <= MAX(CurrentMonth[CurrentMonthParameter])))
VAR Forecast_not_cumulated = CALCULATE([Total Forecast],FILTER(IN_Forecast, IN_Forecast[Date] > MAX(CurrentMonth[CurrentMonthParameter])))
RETURN
IF((SELECTEDVALUE('LT_Reporting Calendar'[Date]) <= MAX(CurrentMonth[CurrentMonthParameter])),Actual_Hrs_not_cummulated, Forecast_not_Cumulated)

This one gives me the line chart of the non cumulated Forcast, and it works.

But as soon as I want to build the cumulative sum on the measure above according to the measure below, I get only the cumulative sum till current month and the future is omitted. I think I have a filter issue. I've tried many methods of building a cumulative sum, always getting the same result.

Chart_Forcast Cumulated = 
CALCULATE(
    [Chart_Forecast not cumulated],
    FILTER(
        ALL('LT_Reporting Calendar'[Date]),
        'LT_Reporting Calendar'[Date] <= MAX ('LT_Reporting Calendar'[Date])
    )
)

Input:

Input_ActualsInput_Forcast

Result: The Chart enter image description here Does any body have an Idea on this?

Many Thanks in advance

1
powerbi
Can you show the data you are working with and what end result your are expecting? – W.B.
Data added. Thx – Alseny
Please add your data as formatted text rather than image. – W.B.

1 Answers

1
votes

I'd suggest writing it as a sum like this:

Cumulative =
VAR ParameterDate = SELECTEDVALUE ( CurrentMonth[CurrentMonthParameter] )
VAR AxisDate = SELECTEDVALUE ( 'LT_Reporting Calendar'[Date] )
RETURN
    CALCULATE (
        [Total Actuals],
        'LT_Reporting Calendar'[Date] <= AxisDate,
        'LT_Reporting Calendar'[Date] <= ParameterDate
    ) +
    CALCULATE (
        [Total Forecast],
        'LT_Reporting Calendar'[Date] <= AxisDate,
        'LT_Reporting Calendar'[Date] > ParameterDate
    )