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
8 years ago

v-jiascu-msft

 

Thanks again!

 

Finally got around my problems mentioned previously. I think there was really an issue with dates.

 

Still got one more problem in daily stock calculations.

(I will definitely open a new thread when this is solved. I think the problem has to do with original question its solution - and therefore I placed my question here.)

 

Accumulated value is currently counted like this:

AccumulatedValue = CALCULATE (
SUM ( 'Stock entries'[Quantity] );
FILTER ( ALL ( 'Calendar' ); 'Calendar'[DateKey].[Date] <= MAX('Stock entries'[Posting Date]))

 

Accumulated value isn't counted in the table below at all if there is no stock movements for that particular date.

(As the trigger for counting is posted line of stock movements and there is none for second day.)


What would be the solution to force counting for this day as well?

 

 

Cheers,

Jenny

 

 

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

dhannaa,

 

Hi Jenny,

 

Is the table your visual or source data? There is no date in it. That's why it didn't work. If you have dates, please try to use the dates from table Calendar. A right relationship is also needed.

 

Best Regards!

Dale

  • dhannaa's avatar
    dhannaa
    Helper IV
    8 years ago

    v-jiascu-msft

     

    Sorry my picture did not tell it all. It was a visual table and there was a calendar table linked to the fact table (dates of my picture/visual were from calendar table), but there must have been something wrong with the calendar table. I anyway managed to tackle all the mysterious problems by remaking my file.  Yes!

     

    Jenny