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

 


(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

    • dhannaa's avatar
      dhannaa
      Helper IV
      9 years ago

      Thanks again v-jiascu-msft !

       

      This solution worked, I kind of figured it out myself through trial and error :)

       

       

      I've got one more question, is there an easy answer for this? I can't find one though I am trying hard.

       

      I am trying to achieve this kind of table that tells stock value for any given month. For now I am only getting stock changes as source data is filtered by BI matrix table (and its dates) itself? Is it possible to bypass that filtering with DAX-formula as I want cumulated data from beginning of time, not beginning of month?

       

       

       

      Jenny

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

        dhannaa,

         

        Hi Jenny,

         

        I found out that changing "min" into "max" would work.

         

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

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale