Forum Discussion

dhannaa's avatar
dhannaa
Helper IV
9 years ago
Solved

Cumulative sum

Hi all,   I am trying to achiece a stock report that gives stock value per different products for any given date.    To achieve this - and other cool things as well - I would like to have a new c...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    dhannaa,

     

    Hi Jenny,

     

    I guess you have a date table in this scenario. Let's call it "Calendar". Then you can try this formula. 

    AccumulatedValue =
CALCULATE (
    SUM ( Table1[Qty] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MIN ( 'Calendar'[Date] ) )
)

    Add a date slicer from "Calendar".

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

dhannaa's avatar
dhannaa
Helper IV
9 years ago

Hi again v-jiascu-msft,

 

I tested your suggestion further and after all it didn't work the way I wanted. Sorry if I was being unclear at first. Below is table of what I need:

 

 

Every row represents one stock movement and to get stock for certain date I need cumulative sum from the beginning of time. Now in your example cumulative sum is counted within certain date - this would tell only how stock changes on that day.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi dhannaa,

 

It's easy to modify the formula. I wounder if a calculated column is good enough.

Cumulative sum =
VAR currentDate = 'Table1'[Posting Date]
VAR currentItem = 'Table1'[Item]
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            'Table1',
            Table1[Posting Date] <= currentDate
                && 'Table1'[Item] = currentItem
        )
    )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale