topic Re: Cumulative total across measure value in DAX Commands and Tips

Cumulative total across measure value

biswad — Fri, 31 Jan 2025 14:00:41 GMT

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

Re: Cumulative total across measure value

bhanu_gautam — Fri, 31 Jan 2025 14:16:27 GMT

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.

Re: Cumulative total across measure value

johnt75 — Fri, 31 Jan 2025 14:22:08 GMT

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

Re: Cumulative total across measure value

biswad — Fri, 31 Jan 2025 16:30:41 GMT

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

Re: Cumulative total across measure value

biswad — Fri, 31 Jan 2025 16:31:21 GMT

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