Forum Discussion

sy898661's avatar
sy898661
Helper V
6 years ago
Solved

rolling inventory over time

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 ❤️ 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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