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

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