Cumulative stock development

Franz · June 17, 2021, 9:18am

Hello all,
i kindly ask for your help, after a few attempts and searches i was not able to find the correct soultion for my issue:

it is about stock development, i have 3 tables (+date table)

Inbounds (date / Item / quantity)
Outbounds (date / Item / quantity)
itemtable (to aggregate result on itemgroups)

what i am trying to do is cumulate the stock development on Year-month base, so in short addup the difference between in and outbounds on monthly base. IT should be a measure which works on items and also on groups

this is what my desired result looks like

i prepared a simple testfile, hope i was able to explain my issue
pleasehelp.pbix (101.6 KB)

thanks a lot for your help, greetings from austria
FRanz

BrianJ · June 17, 2021, 10:05am

@Franz ,

Give this a go:

Cumul Delta =

VAR Cumul = 
CALCULATE(
   [Delta In vs. Out],
   FILTER(
       ALLSELECTED( dim_Kalender  ),
        dim_Kalender[SortYearMonth] <= MAX(dim_Kalender[SortYearMonth] )
    )
)

RETURN
IF( [Delta In vs. Out] = BLANK(), BLANK(), Cumul )

Also, note that the CALCULATE functions in your other measures aren’t doing anything, because you are not changing context, and thus should be removed.

I hope this is helpful. Full solution file attached.