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

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

      • dhannaa's avatar
        dhannaa
        Helper IV
        9 years ago

        v-jiascu-msft

         

        Thanks Dale! You rock ;)

         

        Jenny