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

 

  • dhannaa's avatar
    dhannaa
    Helper IV
    9 years ago

     


    (I edited my post a little as I thought my case through one more time)

     

    ------

    Again thank you very much for your response v-jiascu-msft! As said earlier I am new to PowerBI and not a coder at all. But I am eager to learn it and driving our organization towards using it. So getting your help is highly important to me, appreciate it a lot!

     

    Your solution seems to work fine, but it actually seems my that own plan is not working the way I wanted.

     

    Every product does not have stock movement every day - and selecting one date gives me only stock qtys for products with rows on that particular date. Not the whole stock  (other products as well) on that day.  Perfectly logical after all.

     

    The counting of calculatied columns itself is pretty slow and if I end up calculating values for every single date it propably ain't wise at all.

     

    Could my goal be achieved with DAX? With a formula that gives cumulative qty for selected date - and if selected date doesn't exist (in the stock movement data) the formula would look the value of the previous date, if not found then the previous and so on until a value is found.

     

    Cheers, Jenny

    • dhannaa's avatar
      dhannaa
      Helper IV
      9 years ago

      Is my idea (described above) possible by a any means? Or does it have some error in logic I don't realize?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        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