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[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])) ) None of them worked. Here is the columns of my table: ForeCast_Value / Forecast_Date / Month / MonthNumber / ForeCast_BU / ForeCast_Year / ForeCast_ID And some examples of records: 71100 / Saturday, September 1, 2018 / Sep / 9 / 105 / 2018 / 10648 71100 / Monday, October 1, 2018 / Oct / 10 / 2018 / 10648 81000 / Saturday, September 1, 2018 / Sep / 9 / 105 / 2018 / 10649 71200 / Saturday, September 1, 2018 / Sep / 9 / 120 / 2018 / 10700 My first report has Month on the axis, ForeCast_BU on the legend and ForeCast_Value on Values. I want my second report as Month on the axis, ForeCast_BU on the legend and cumulative ForeCast_Value on Values.
Need Help

4 Replies

  • 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
    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi oc,

       

      Sorry for the delay.

       

      If it is convenient, could you share your data sample as table format in here and your desired output so that we could help further on it?

       

      Hope you could paste your data here this time.

       

      Best  Regards,

      Cherry