Deliveries + Inventory DAX Measure

Hey all - I have been struggling on this for days and tried everything that I could think of, but I can't seem to get it to work:

OVERALL Purpose:

This dashboard needs to calculate the Current Inventory, minus the monthly run rate + Deliveries.

The part I am having major issues with is the deliveries.

I have 3 tables:

• Stock Data

• Calendar

• Order Data

They have the following Relationships

• Stock Data -- Order Data -- Connection = SKU TITLE -- ACTIVE

• Stock Data -- _Calendar -- Connection = Date -- ACTIVE

• Order -- _Order Calendar -- Connection = Date = ACTIVE

Stock Data contains key columns:

• Quantity Available

• Run Rate

• SKU Title

• First Day of this month (calculated Column)

ADM OIH Data Contains key columns:

• Title

• Total Pieces

• Delivery Month (i.e. 01/09/2023)

Currently, I have three measures, one of which works fine - two of which there are issues:

_mProjectedStock = 
VAR AvailableDate = EOMONTH(TODAY(), -1) + 1
VAR RunRate = CALCULATE(
    SELECTEDVALUE('Stock Data'[Run Rate]),
    ALLEXCEPT('Stock Data', 'Stock Data'[Title])
)

VAR CumulativeDeliveries =
    FILTER (
        ALL ( '_Calendar' ),
        '_Calendar'[Date] >= AvailableDate
            && '_Calendar'[Year] = YEAR ( '_Calendar'[Date] )
            && '_Calendar'[Month] = MONTH ( '_Calendar'[Date] )
    )

VAR Deliveries =
    SUMX (
        CumulativeDeliveries,
        CALCULATE (
            SUM ( 'Order Data'[Total Pieces] )
        )
    )

VAR CurrentMonthQty =
    CALCULATE(
        SUM('Stock Data'[Quantity Available]),
        FILTER(
            ALL('_Calendar'),
            YEAR('_Calendar'[Date]) = YEAR(AvailableDate) &&
            MONTH('_Calendar'[Date]) = MONTH(AvailableDate)
        )
    )

VAR MonthsDiff =
    DATEDIFF(AvailableDate, MIN('_Calendar'[Date]), MONTH)
VAR RemainingMonths = MAX(0, MonthsDiff)
VAR Calc2 = CurrentMonthQty - (RunRate * RemainingMonths)
RETURN
    ROUNDUP(Calc2, 0)

This calculates stock perfectly regarding the run rate

This is the formula I am having problems with:

_mProjected+Deliveries = VAR AvailableDate = EOMONTH(TODAY(), -1) + 1 VAR RunRate = CALCULATE( SELECTEDVALUE('Stock Data'[Run Rate]), ALLEXCEPT('Stock Data', 'Stock Data'[Title]) )

VAR CumulativeDeliveries =
    FILTER (
        ALL ( '_Calendar' ),
        '_Calendar'[Date] >= AvailableDate
            && '_Calendar'[Year] = YEAR ( '_Calendar'[Date] )
            && '_Calendar'[Month] = MONTH ( '_Calendar'[Date] )
    )

VAR Deliveries =
    SUMX (
        CumulativeDeliveries,
        CALCULATE (
            SUM ( 'Order Data'[Total Pieces] )
        )
    )

VAR CurrentMonthQty =
    CALCULATE(
        SUM('Stock Data'[Quantity Available]),
        FILTER(
            ALL('_Calendar'),
            YEAR('_Calendar'[Date]) = YEAR(AvailableDate) &&
            MONTH('_Calendar'[Date]) = MONTH(AvailableDate)
        )
    )

VAR MonthsDiff =
    DATEDIFF(AvailableDate, MIN('_Calendar'[Date]), MONTH)
VAR RemainingMonths = MAX(0, MonthsDiff)
VAR Calc2 = CurrentMonthQty + Deliveries - (RunRate * RemainingMonths)
RETURN
    ROUNDUP(Calc2, 0)

So what is happening is it is putting all the deliveries into the first month, not the right month.
I have tried fixing this with relationships - but it doesn't seem to work:

 

 

Item 1, as you can see, should be in the September part - but instead, it is in August - which is obviously due to the fact there is no direct relationship between Calendar table and Order Table - but when I try to alter it, it breaks on the overall count - because it just starts summing up ALL the SKU titles.

I am at a loss and would love some help!

I have a sample PBIX file I am happy to share - just can't see an attachment button