cancel
Showing results for
Did you mean:
Member

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

Accepted Solutions
Senior Member

Re: Cumulative sum of a column

Hi @ssvr

Here is a nice formula for cumulative column

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

- Quentin

10 REPLIES 10
Senior Member

Re: Cumulative sum of a column

Hi @ssvr

Here is a nice formula for cumulative column

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

- Quentin

Member

Re: Cumulative sum of a column

Thanks it works fine.

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

@ssvr

You're welcome

Regular Visitor

Re: Cumulative sum of a column

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

Senior Member

Re: Cumulative sum of a column

@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

Regular Visitor

Re: Cumulative sum of a column

Hi,

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

Senior Member

Re: Cumulative sum of a column

@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

Highlighted
Member

Re: Cumulative sum of a column

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

Regular Visitor

Re: Cumulative sum of a column

@quentin_vigne

Thanks. It is working correctly now

Announcements

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Get your latest community news and announcements.

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 375 members 3,440 guests
Recent signins: