rolling inventory over time

[sy898661]

Hi all,

I have posted about this before and got some great responses--but now my problem has become more complicated and no matter what I try it is not working out.

(I hope it is not against community guidelines to post again, I had asked this more complicated question in the comments of my more simple question after it had been marked as solved so I dont think anyone went to look at it) 

Prev post with more simple ask: https://community.powerbi.com/t5/Desktop/Show-total-decrease-over-time/m-p/951859#M456099

So now I was wondering how I could either edit this code from my previous ask (or create a new code).

Measure =
VAR w =
    CALCULATE ( MAX ( 'Table'[Week] ), ALLSELECTED ( 'Table'[Item] ) )
VAR i =
    SELECTEDVALUE ( 'Table'[Item] )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Item] = i && 'Table'[Week] > w ),
        'Table'[Qty]
    )

What the code currently does:

Subtracts on hand qty from total (sum of all on hand qty) as the Ship Date passes

Now let's make it more complicated...

What I now would like it to do:

If Start Date has already passed, Use as starting On-Hand qty (lets call it TOTAL qty)

Subtract on hand qty from TOTAL as Ship Date passes

Add on hand qty to TOTAL as Start Date passes

Here is a simple example that I made in excel to visually show what I mean:

https://drive.google.com/file/d/1e--PjJHbpJbuYFDvaU2c-NmMRicrf29o/view?usp=sharing

^google drive link for the excel file so you can see it better 

Is this possible? Are we trying to be too ambitious with this? Any help will be much appreicated ❤️ 

[sy898661]

Ahhh thank you both so much!!!! I cannot tell you how awesome this is! @v-lili6-msft @AiolosZhao 

[v-lili6-msft · Accepted solution]

hi  @sy898661 

How do you define [current day], I assume it is a spearate measure.

Then add a spearate dim week bin table

now try this measure logic:

Measure = 
VAR i =
    SELECTEDVALUE ( 'Table'[Item Number] )
RETURN
    IF (
        [current day] > MAX ( 'week Bin'[Ship Week bin] ),
        CALCULATE (
            SUM ( 'Table'[On Hand Qty] ),
            FILTER (
                'Table',
                'Table'[Planned Start Date] <= [current day]
                    && 'Table'[Item Number] = i
            )
        ),
        CALCULATE (
            SUM ( 'Table'[On Hand Qty] ),
            FILTER (
                'Table',
                'Table'[Planned Start Date] <= MAX ( 'week Bin'[Ship Week bin] )+6
                    && 'Table'[Item Number] = i
            )
        )
    )
        - CALCULATE (
            SUM ( 'Table'[On Hand Qty] ),
            FILTER (
                'Table',
                'Table'[Ship Week bin] <= MAX ( 'week Bin'[Ship Week bin] )
                    && 'Table'[Item Number] = i
            )
        )

Result:

Regards,

Lin

[AiolosZhao]

Hi @sy898661 ,

Hope the chart in the below screenshot is what you want:

1. I created a new table with one column to be the axis in the chart.

Table 2 = SELECTCOLUMNS('Table (6)',"New WEEK BIN",'Table (6)'[Ship Week bin])

2. Then I created a new measure in the new table to be the values in the chart.

Measure 5 = CALCULATE(SUM('Table (6)'[On Hand Qty]),FILTER('Table (6)',AND(WEEKNUM('Table (6)'[Planned Start Date]) <= WEEKNUM(MAX('Table 2'[New WEEK BIN])),WEEKNUM('Table (6)'[Ship Date]) > WEEKNUM(MAX('Table 2'[New WEEK BIN])))))

Please try.

Aiolos Zhao