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

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

Hi dhannaa,

 

Just see from your data, you could try this formula. I added some more data to make it clear.

 

Cumulative sum =
CALCULATE ( SUM ( Table1[Qty] ), ALL ( Table1[Qty] ) )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

  • dhannaa's avatar
    dhannaa
    Helper IV
    9 years ago

    Thank you for your suggestions! I will try them and get back if (and when) I have more challenges :) !

    Actually I have a calendar table related to the fact table but I am not sure if it is correctly set up at the moment.

    • dhannaa's avatar
      dhannaa
      Helper IV
      9 years ago

      I tried this out and everything worked like a charm. I was being way too complicated with my DAX :)

       

      Can you furthermore clarify me on what would be the most efficient way to filter / show this stock value per date -data.

       

      I have a huge amount of dates and I would like obtain a situation where user selects certain date (one date, not a time period) and gets immediately stock value for that moment in time. 

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

        Hi dhannaa,

         

        I would suggest the custom visual "HierarchySlicer", which is easy to locate the dates.

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        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