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 column for each cumulative sum (per given item and date).

 

 

I have tried many ways and my code just doesn't seem to work... I am new to Power BI and certainly not a coder :)

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

20 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    first - create a dedicated calendar table with DAX, by using the CALENDAR(...) function, you may also consider using M within your QueryEditor to build the calendar table, but this almost depends on your personal taste

    2nd - create a relationship between your table (m-side) and your calendar table (1-side)

    3rd - http://www.daxpatterns.com/time-patterns/

     

    hope this gets you started

  • 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. 

    • 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