Forum Discussion

todebrownie's avatar
todebrownie
Regular Visitor
4 years ago

Inventory Projection

Hello Community, how are you?   I'm new to PowerBI and I'm trying to create a inventory projection. I would like to plot in a weekly basis (every monday) and would use some reports as starting poi...
Need Help
v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi todebrownie ,

 

Not sure about your data structure, I created the following example data based on my understanding.

 

 

Then create these measures:

 

Weekly Forecast = 
IF (
    MAX ( 'Calendar'[WeekDay] ) = 1,
    CALCULATE (
        SUM ( 'Daily Stock'[Consumption] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[WeekNum] = MAX ( 'Calendar'[WeekNum] )
        )
    )
)
SumShipments = 
IF (
    MAX ( 'Calendar'[WeekDay] ) = 1,
    CALCULATE (
        SUM ( 'Daily Stock'[Shipments] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[WeekNum] = MAX ( 'Calendar'[WeekNum] )
        )
    )
)
ForecastInventory = 
VAR current_Mo =
    CALCULATE (
        MIN ( 'Daily Stock'[Date] ),
        KEEPFILTERS ( 'Calendar'[WeekDay] = 1 ),
        ALLSELECTED ( 'Calendar' )
    )
VAR stock =
    CALCULATE (
        SUM ( 'Daily Stock'[Inventory] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] = current_Mo )
    )
VAR W_S =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] < MAX ( 'Calendar'[Date] )
        ),
        [Weekly Forecast] - [SumShipments]
    )
RETURN
    IF (
        MAX ( 'Calendar'[Date] ) = current_Mo,
        SUM ( 'Daily Stock'[Inventory] ),
        IF (
            MAX ( 'Calendar'[WeekDay] ) = 1
                && MAX ( 'Daily Stock'[Date] ) <> BLANK (),
            stock - W_S
        )
    )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.