Calculating cumulative values

Question

I am trying to calculate cumulative values. I tried the formulas below, none of them worked.

Cumulative_Forecast = CALCULATE(sum(TEAMS_Forecast_LineItems[ForeCast_Value]),
filter(ALLEXCEPT(TEAMS_Forecast_LineItems,TEAMS_Forecast_LineItems[ForeCast_Year]), 
TEAMS_Forecast_LineItems[MonthNumber]<=EARLIER(TEAMS_Forecast_LineItems[MonthNumber])))



Cumulative_Forecast2 = VAR RowDate = TEAMS_Forecast_LineItems[Forecast_Date]
return CALCULATE(sum(TEAMS_Forecast_LineItems[ForeCast_Value]), 
FILTER(TEAMS_Forecast_LineItems, TEAMS_Forecast_LineItems[Forecast_Date]
<=RowDate && YEAR ( TEAMS_Forecast_LineItems[Forecast_Date] ) = YEAR ( RowDate ))) 



Cumulative_Forecast3 = TOTALYTD(sum(TEAMS_Forecast_LineItems[ForeCast_Value]), 
'Calendar'[Date]) 



Cumulative_Forecast4 = CALCULATE(sum(TEAMS_Forecast_LineItems[ForeCast_Value]),
filter(ALL(DimDate[Date]), DimDate[Date] <= Max(DimDate[Date])) )

Here are some examples of records:

ForeCast_Value   Month   MonthNumber     ForeCast_BU     ForeCast_Year   ForeCast_ID
71100   Sep 9   Business1   2018    10648
71100   Oct 10  Business1   2018    10648
81000   Sep 9   Business1   2018    10649
71200   Sep 9   Business2   2018    10700
80500   Sep 9   Business2   2017    10500
80600   Oct 10  Business2   2017    10500
81100   Sep 9   Business2   2018    10650

I have a line chart; Month on the Axis, BU on the legend and value on values.

When slicer year is all selected

Business 1= 152100 and Business 2 =232800 on September 
Business 1= 71100 and Business 2 =80600 on October

When slicer year is 2017 selected

Business 2=80600 on Sep 
Business 2=80500 on Oct

I want to create a new line chart for cumulative values. Desired values are:
When slicer year is all selected

Business 1= 152100 and Business 2 =232800 on September 
Business 1= 223200 and Business 2 =313400 on October

When slicer year=2017 selected

Business 2=80600 on Sep 
Business 2=161100 on Oct

KDP KDP · Accepted Answer · 2019-01-22T10:11:15

The below DAX formulas can help you to get your expected results:

Cumilative_Forecast = CALCULATE ( SUM ( TEAMS_Forecast_LineItems[ForeCast_Value] ), FILTER(ALL(TEAMS_Forecast_LineItems),TEAMS_Forecast_LineItems[ForeCast_BU] = max ( TEAMS_Forecast_LineItems[ForeCast_BU] )) , FILTER(ALL(TEAMS_Forecast_LineItems), TEAMS_Forecast_LineItems[MonthNumber] = max ( TEAMS_Forecast_LineItems[MonthNumber] )),FILTER ( ALL ( TEAMS_Forecast_LineItems ), TEAMS_Forecast_LineItems[ForeCast_Year] <= max( TEAMS_Forecast_LineItems[ForeCast_Year] ))
)

The above formula gives result of " When slicer year is all selected

Business 1= 152100 and Business 2 =232800 on September Business 1= 71100 and Business 2 =80600 on October "

Cumulative_Value = CALCULATE ( SUM ( TEAMS_Forecast_LineItems[ForeCast_Value] ), FILTER(ALL(TEAMS_Forecast_LineItems),TEAMS_Forecast_LineItems[ForeCast_BU] = max ( TEAMS_Forecast_LineItems[ForeCast_BU] )) , FILTER ( ALL ( TEAMS_Forecast_LineItems ), TEAMS_Forecast_LineItems[MonthNumber] <= max( TEAMS_Forecast_LineItems[MonthNumber] ))
)

The above formula gives result of "I want to create a new line chart for cumulative values. Desired values are: When slicer year is all selected

Business 1= 152100 and Business 2 =232800 on September Business 1= 223200 and Business 2 =313400 on October "

Please let me know does this reached your expected result or not.

Calculating cumulative values

1 Answers