cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheoM Regular Visitor
Regular Visitor

running total in calculated column

Hi all,

 

My data model contains a table with planned changes in stock per item. I want to add a column to this table which contains the accumulated stock changes for this item until the date of the current row. I am sure there must be an easy way to do this, but I haven't found the right way yet. Can anyone help me?

 

Regards,

Theo

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: running total in calculated column

@TheoM

 

OK, try this:

 

RunningTotal =
CALCULATE (
    SUM ( Table1[Movement] ),
    Table1[Date] <= EARLIER ( Table1[Date] ),
    ALLEXCEPT ( Table, Table1[Item] )
)

It's always good to show your sample data also on table/text format so that it can be readily copied

 

View solution in original post

4 REPLIES 4
Super User
Super User

Re: running total in calculated column

Hi @TheoM

 

You need to show the structure of your data model and sample data to make things easier for people answering.

What do you mean by 'accumulated stock changes'?? The sum? An example based on your sample data would certainly help.

 

NewColumn =
CALCULATE (
    SUM ( Table1[Stock] ),
    FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) )
)

but you provide too little info

TheoM Regular Visitor
Regular Visitor

Re: running total in calculated column

Hi @AlB,

Thanks for your quick reply!

Here is simplified structure of my fact table. It contains various items which have multiple planned changes. There can be several changes on the same item at the same date (see line 4 and 5). In order to calculate the planned stock level at the date shown on the current row, I want to sum all changes until the date in the current row. The Running Total column shows the desired result. FYI: date format is d-m-yyyy

I hope you can help me with this.

 

 Data model.JPG

 

 

Highlighted
Super User
Super User

Re: running total in calculated column

@TheoM

 

OK, try this:

 

RunningTotal =
CALCULATE (
    SUM ( Table1[Movement] ),
    Table1[Date] <= EARLIER ( Table1[Date] ),
    ALLEXCEPT ( Table, Table1[Item] )
)

It's always good to show your sample data also on table/text format so that it can be readily copied

 

View solution in original post

TheoM Regular Visitor
Regular Visitor

Re: running total in calculated column

Hi @AlB

 

That did the job! Tanks for your help and your advice regarding additional info to the problem.

 

Regards, Theo

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: 124 members 1,556 guests
Please welcome our newest community members: