Cumulative total across measure value

[biswad]

Hi everyone, 

I have few measures which calculate Total number of case created , case closed , net change per month. 

I would like to create another measure called "Stock" which will add the value of "Net Change" into next month. For example we start with stock value as 7 which is the "Net Change" value for the month of January , as we move to February I would like the measure to calculate stock value of February as sum of January Stock value  + Net Change of February  = 7+2= 9.

Similary I would like to calculate the Stock of March as sum of February Stock Value +March Net Change =9+2=12.

Any help on how to calculate the stock column would be greatly appriciated.

Month	Total Created	Total Close	Net Change	Stock
January	14	7	7	7
February	5	3	2	9
March	12	10	2	12
April	6	4	2	14
May	2	10	-8	6

[johnt75 · Accepted solution]

There's a mistake in your sample data. March should be 9+2 = 11, not 12.

What you actually need to calculate here is the running total of net change for all months up to and including the current month. You can do that with

Stock =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR DatesToUse =
    CALCULATETABLE ( VALUES ( 'Date'[Year month] ), 'Date'[Date] <= ReferenceDate )
VAR Result =
    SUMX ( DatesToUse, [Net change] )
RETURN
    Result

[biswad]

Thank you so much, it took me  a while to understand the logic. much appriciated your help. 

[bhanu_gautam]

@biswad , Create a new measure for Stock:

DAX
Stock =
VAR CurrentMonth = MAX('Table'[Month])
RETURN
CALCULATE(
SUM('Table'[Net Change]),
FILTER(
ALL('Table'),
'Table'[Month] <= CurrentMonth
)
)

Make sure that your data is sorted by the "Month" column in the correct order. You can do this by creating a sort column if necessary.

Add the "Stock" measure to your table visual in Power BI. This will display the cumulative total of the "Net Change" for each month, effectively giving you the "Stock" value as described.

[biswad]

thank you for your response brother, but my data is taking the month from a created on column which is already in the table. The below query solves the issue. much appriciated. 

Stock =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR DatesToUse =
    CALCULATETABLE ( VALUES ( 'Date'[Year month] ), 'Date'[Date] <= ReferenceDate )
VAR Result =
    SUMX ( DatesToUse, [Net change] )
RETURN
    Result