Forum Discussion

kskumar's avatar
kskumar
Frequent Visitor
2 years ago

Cumulative sum required based on two measures - help required

Dear all,   I have 3 tables and a DateTable and need to get the Cumulative sales for the entire period (inception to till date). However, I have to use a slicer to filter the data for the current y...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

kskumar , Simply create a cumulative measure for that

 

example by using sales table

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))


Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

 

You can also consider new visual calc runningsum

Master Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

  • kskumar's avatar
    kskumar
    Frequent Visitor
    2 years ago

    Hi amitchandak ,

    Thanks for your response.  I tried creating a measure as you suggested and captured the screenshot below. Unfortunaltely, not getting the result that I need.

    ----------------------------------------------------------------------------------

    ActualsFullYearsinceInception =
    VAR selmonth = MAX(DateTable[MonthNumber])
    VAR selfiscalYrSort = MAX(DateTable[FiscalYearSort])
     
    VAR PrevYr = CALCULATE(SUM(CostCtrlNew[Actual and Forecast]), FILTER(All(DateTable),DateTable[FiscalYearSort] < selfiscalYrSort))
     
    VAR CurYr = CALCULATE(SUM(CostCtrlNew[Actual and Forecast]), FILTER(ALL(DateTable),DateTable[FiscalYearSort] = selfiscalYrSort && DateTable[MonthNumber] = selmonth))

    RETURN PrevYr+CurYr

    -----------------------------------

    CumSum =
    CALCULATE([ActualsFullYearsinceInception],WINDOW(1,ABS,0,REL,ALL(DateTable),ORDERBY(DateTable[Date],ASC)))

     

    Have attached the PBIX file for your reference as well. What I need is the cumulative sum for the actuals listed as the last column.

     

    https://drive.google.com/file/d/1mxV_ljuWwT70TCpfIbu7i0Ch-TLqLwA_/view?usp=sharing

    https://docs.google.com/spreadsheets/d/1y6ViaF7ZJwNFy7CMgwZ457i0yHJAudzO5mS7pRFah4I/edit?usp=drive_link

     

     

    Thank you.