Forum Discussion

oc's avatar
oc
New Member
7 years ago

Calculating cumulative values

I am trying to calculate cumulative values. I tried: Cumulative_Forecast = CALCULATE(sum(TEAMS_Forecast_LineItems[ForeCast_Value]), filter(ALLEXCEPT(TEAMS_Forecast_LineItems,TEAMS_Forecast_LineItems[F...
Need Help
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi oc,

 

By my test, it seems that your formulas are calculated columns. I'm afraid that you'd better create measure to calculate the cumulative values.

 

Please try to create the measure with the formula below.

 

Measure =
CALCULATE (
    SUM ( TEAMS_Forecast_LineItems[ForeCast_Value] ),
    FILTER (
        ALLEXCEPT ( TEAMS_Forecast_LineItems, TEAMS_Forecast_LineItems[ForeCast_Year] ),
        TEAMS_Forecast_LineItems[MonthNumber]
            <= MAX ( 'TEAMS_Forecast_LineItems'[MonthNumber] )
    )
)

If you still need help, please share your data sample with table format so that I can copy and test with it, and your desired output about cumulative values so that I can get the solution quickly.

 

Best  Regards,

Cherry

  • oc's avatar
    oc
    New Member
    7 years ago
    It didn`t work. Here are some part of my data. Sorry I cant add picture to the message because there are some js errors on page. I have a line chart; Month on the Axis, BU on the legend and value on values. When slicer year=all is selected Business 1= 152100 and Business 2 =232800 on September Business 1= 71100 and Business 2 =80600 on October When slicer year=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=all is 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 According to this data: 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