Cumulative sum

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

dhannaa,

Hi Jenny,

So how is your weekend? In most of the scenarios, the function "Sameperiodlastyear" is enough with a context "Month' in the report. Surely there are still many other ways. 

Reference: https://msdn.microsoft.com/en-us/library/ee634972.aspx

Best Regards!

Dale

My weekend is long gone :smileylol: , but I did start my learning process on DAX  - it will be some project though  ;)

With this formula

LY AccumulatedValue 2 =
CALCULATE([AccumulatedValue]; SAMEPERIODLASTYEAR('Calendar'[DateKey].[Date]))

I get the totally same value for this and last year.. not correct. (I am using matrix table, months in rows, year in columns)

(AccumulatedValue  formula is 

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

))  )

What goes wrong?

It seems that Accumulated value - formula gives sum of qtys for certain range filtered by reports dates no matter what. There is this "take last posting date and sum all the quantities to that moment" -logic in accumulated values -> this seems not to work with SAMEPERIODLASTYEAR as it seems that day filtering is taken from the report and qty values are still summed up according to tables dates, not last year.

I have tried many workarounds but keep getting errors and wrong values.

BR, Jenny

dhannaa,

Hi Jenny,

I think this could be something wrong with the context. Where are the months and year from?

BTW, it would be a good idea to open a new thread. Maybe community members want to search solutions. One topic, one solution. Thank you for you understanding.

Best Regards!

Dale

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

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