Forum Discussion
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 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
3 Replies
- amitchandak
Super User
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=42You 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=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8LHi 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
Thank you.
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.
