cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ssvr
Helper III
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
quentin_vigne
Solution Sage
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

View solution in original post

11 REPLIES 11
quentin_vigne
Solution Sage
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

View solution in original post

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

@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

Hi,

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

@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

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

@quentin_vigne

        Thanks. It is working correctly now

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

@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, and Sales Analysis Report

Thanks it works fine.

 

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

@ssvr

 

You're welcome

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.