Cumulative sum required based on two measures - help required

[kskumar]

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 year and that filtered data for current year should be considered for cumulative.

Table 1: CostCtrlNew

Table2: Prog Proj Master

Table3: ProgBudget Mly

Requirement: I need to choose Cumulative sum from CostCtrlNew table. 

Relationship:

Expected Cumulative SUM as shown below.

Measure 1: To consider all records for previous year (prior to current fiscal year) - I have a measure already

Measure 2: To consider ONLY records for current year (fiscal year = 2023-2024) and for the chosen month (based on a slicer from DateTable) - I have a measure already

I need a cumulative sum of both measures (Measure1 & Measure2) and show Fiscal Year wise.

I have enclosed the PBIX and source file for reference. Can SOMEONE please help on this to get cumulative sum for my requirement.

Kindly let me know if you need any further information.

https://drive.google.com/file/d/1ne5xfl4Bceqa7DL3pey1PxOE2vnofAkW/view?usp=drive_link

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

[kskumar]

Hi @xifeng_L ,  

Last time you helped me on a similar requirement. Can you please help me for this new requirement.

Solved: Cumulative total with a slicer did not give the cu... - Microsoft Fabric Community

Thank you.

[amitchandak]

@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]

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_l...

Thank you.