cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper III

## Cumulative sum of a column

Hi,

I want to create a new column (Cumulative sales)

any one provide the DAX for new column

Thanks

1 ACCEPTED SOLUTION
Solution Sage

Hi @ssvr

Here is a nice formula for cumulative column

```Cumulative sales =
CALCULATE (
SUM ( 'YourTable'[Sales] ),
ALL ( 'YourTable)' ),
'YourTable'[Date] <= EARLIER ( 'YourTable'[Date] )
)```

- Quentin

13 REPLIES 13
Helper I

Hi i've been using this formula and it works great. However when i use slicers that are not related to the date, it will not update the quantities. Any ideas ?

Thanks!

Solution Sage

Hi @ssvr

Here is a nice formula for cumulative column

```Cumulative sales =
CALCULATE (
SUM ( 'YourTable'[Sales] ),
ALL ( 'YourTable)' ),
'YourTable'[Date] <= EARLIER ( 'YourTable'[Date] )
)```

- Quentin

Helper I

Hi i've been using this formula and it works great. However when i use slicers that are not related to the date, it will not update the quantities. Any ideas ?

Thanks!

Helper I

If there are 2 Rows for same date it is showing double value for that row. Am I doing any thing wrong

Solution Sage

@pnvinod

What does your date column looks like ?

dd/mm/yyyy hh mm ss or only dd/mm/yyyy ?

This formula work for distinct rows, on a distinc index value.

- Quentin

Helper I

Hi,

Thanks. I am using "dd/mm/yyyy"

Solution Sage

@pnvinod

So if you have a lot of sales each day and a row for each sales it won't work.

The solution will be to :

Either have a Index column (like Sale1; Sale2 that identify each sale)

OR

Create a new table that use : Table = Summarize(YourTable;YourTable[Date];"Sales";SUM(YourTable[Sales]))

It will display the sum of your sales for each day. And when you will have this table you will be able to create a cumulative column with this table.

- Quentin

Anonymous
Not applicable

If I created an index column for each unique sale how then do I include the index into the DAX formula?

Helper I

@quentin_vigne

Thanks. It is working correctly now

Helper III

For your query this is the solution DAX

Actuals Budget = CALCULATE(
SUM('Gross FCC'[Actuals]),
FILTER(ALLSELECTED('Gross FCC'),
'Gross FCC'[Posting period (r)]<=MAX('Gross FCC'[Posting period (r)])
))

Super User

@ssvr Thank you so much for your solution. I was stuck at the same problem. I was using ALL so getting incorrect values then I replaced it with ALLSELECTED so Instantly I got the expected result.

Thanks a bunch buddy for your solution!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Helper III

Thanks it works fine.

CALCULATE (
SUM ( 'dummy sales'[Sales]),
ALL('dummy sales'),
'dummy sales'[Date] <= EARLIER ( 'dummy sales'[Date])
)
Solution Sage

@ssvr

You're welcome

Announcements

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

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

#### Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors