cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

3 REPLIES 3
Frequent Visitor

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.

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:

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

Frequent Visitor

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.

Thank you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors