cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Cummulative Sum Calculated Column

Hi,

I'm looking to sum cummulative sales month on month per fiscal year(FY) my data is as per the below & the result i'm looking for is a calculated column called "FY Cummulative" as shown.

 Month Name (text) FY Revenue FY Cummulative Apr 23 120000 120000 May 23 100000 220000 Jun 23 200000 420000 Jul 23 300000 720000 Aug 23 100000 820000 Sep 23 200000 1020000 Oct 23 100000 1120000 Nov 23 100000 1220000 Dec 23 200000 1420000 Jan 23 100000 1320000 Feb 23 200000 1520000 Mar 23 100000 1620000 Apr 24 100000 100000 May 24 200000 300000 Jun 24 400000 700000 Jul 24 100000 800000

Can you please advise how i can achieve this with DAX so that its adds up the revenue cummulative each month and then resets the sum once the number in the FY column changes to a new financial year.

1 ACCEPTED SOLUTION
Community Support

Thanks @tamerj1  for your replies, allow me to provide another insight:
1. create new index column in power query editor.

2. create calculated column and write expression.

``````Column =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
ALL('Table'),
'Table'[FY] = EARLIER('Table'[FY]) &&
'Table'[Index] <= EARLIER('Table'[Index])
)
)``````

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Thanks @tamerj1  for your replies, allow me to provide another insight:
1. create new index column in power query editor.

2. create calculated column and write expression.

``````Column =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
ALL('Table'),
'Table'[FY] = EARLIER('Table'[FY]) &&
'Table'[Index] <= EARLIER('Table'[Index])
)
)``````

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Thats great, working as expected. Thanks Clara

Super User

FY Cummulative =
IF (
HASONEVALUE ( 'Date'[FY] ),
SUMX (
FILTER (
CALCULATETABLE (
SUMMARIZE ( 'Date', 'Date'[FY], 'Date'[Month Name] ),
ALLSELECTED ()
),
'Date'[FY] = MAX ( 'Date'[FY] )
),
[Revenue]
)
)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors