Forum Discussion

TheoM's avatar
TheoM
Icon for Helper I rankHelper I
7 years ago
Solved

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

Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

     

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      TheoM
      Icon for Helper I rankHelper I
      7 years ago

      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.

       

       

       

       

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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