running total

[geejee]

hi, i have a table with dates and quantities and for this i need to calculate the stock column and the stock **bleep** column. The stock column is done with a calculated measure in combination with a date table. But how to calculate the stock_cum measure?

[Phil_Seamark]

Hi @geejee

To handel the tricky scenario of the 3-1-2017 having no data I created a new table from your existing table like this

New Table = ADDCOLUMNS(
    DATESBETWEEN(
        'Dates'[Date],
        FIRSTDATE('Table1'[Date]),
        LASTDATE('Table1'[Date])),
        "Quantity",CALCULATE(sum(Table1[Quantity])+0)
        )

I related this new table to my DATE table and then created the following two measures

Stock = CALCULATE(
				SUM('New Table'[Quantity]),
				FILTER(
					ALl(Dates[Date]),
					'Dates'[Date]<=MAX('Dates'[Date])
				)
			)

and

Stock **bleep** = CALCULATE(
				SUMX('New Table',[Stock]),
				FILTER(
					ALl('Dates'),
					'Dates'[Date]<=MAX('Dates'[Date])
				)
			)

You'll see here the Stock **bleep** data increases on the 3rd despite there being no actual data for it to build on  (hence the use of the Calculated Table)

[geejee]

hi Phil,

great I see it works! One more question if I want to for instance add item to the new table to get the stock and stock cumulative per item. How can I do this?

thx,

GJ

[v-huizhn-msft]

Hi @geejee,

You'd better ALLEXCEPT function in DAX, which will calculate the running total in per item. Please refer to this similar thread. If it doesn't resolve your issue, please post your sample data for further analysis.

Best Regards,
Angelia

[Phil_Seamark]

Hi @geejee,

@v-huizhn-msft is correct about using the ALLEXCEPT function.  If you are unsure of how to use, please post some sample data including the column that shows your Item values