cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ssvr Member
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
quentin_vigne Senior Member
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

View solution in original post

10 REPLIES 10
quentin_vigne Senior Member
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

View solution in original post

ssvr Member
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])
)
quentin_vigne Senior Member
Senior Member

Re: Cumulative sum of a column

@ssvr

 

You're welcome

pnvinod Regular Visitor
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

quentin_vigne Senior Member
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

pnvinod Regular Visitor
Regular Visitor

Re: Cumulative sum of a column

Hi,

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

quentin_vigne Senior Member
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
ssvr Member
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)])
))

pnvinod Regular Visitor
Regular Visitor

Re: Cumulative sum of a column

@quentin_vigne

        Thanks. It is working correctly now

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 375 members 3,440 guests
Please welcome our newest community members: