Orderbook adjusted for previous periods differences

Hi NLMV ,

 

You can create a BI report that adjusts the current month's order book based on the cumulative under or over-deliveries from previous periods with the following DAX formula. This measure will dynamically calculate the adjusted amount for each contract.

Adjusted Monthly Distribution =
-- Calculate the originally planned amount for the current period (e.g., the current month)
VAR CurrentMonthDistribution =
    SUM ( 'SalesContractDistribution'[Qty] )

-- Identify the first date of the current period to define the "past"
VAR FirstDateOfCurrentPeriod =
    MIN ( 'Calendar'[Date] )

-- Calculate the cumulative difference between planned and delivered for all periods BEFORE the current one
VAR CumulativePreviousDifference =
    CALCULATE (
        SUM ( 'SalesContractDistribution'[Qty] ) - CALCULATE ( SUM ( 'SalesContractDelivery'[Qty] ), 'SalesContractDelivery'[Status] = "Delivered" ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < FirstDateOfCurrentPeriod
        )
    )

-- Return the current month's plan adjusted by the cumulative past difference.
-- If there was an under-delivery (positive difference), it's added.
-- If there was an over-delivery (negative difference), it's subtracted.
RETURN
    CurrentMonthDistribution + CumulativePreviousDifference

This DAX measure operates by first capturing the originally planned distribution for the current period in a variable. It then determines the start date of this period to serve as an anchor point. The core of the logic lies in the CALCULATE function, which modifies the report's context. It calculates the total historical difference between planned and delivered quantities for all periods before the start of the current one. This is achieved by using FILTER with ALL('Calendar') to remove the current date context and apply a new one for all past dates. The final RETURN statement simply adds this cumulative past difference to the current month's plan. If you under-delivered in the past, the positive difference increases the current month's target; if you over-delivered, the negative difference reduces it.

 

To use this in your report, create a new measure and paste in the DAX code. You can then add this measure to a Matrix visual to see the results. For a comprehensive view, place the 'Sales Contract'[Contract Number] on the rows, the 'Calendar'[Year] and 'Calendar'[Month] on the columns, and then add your original distribution measure, your actual delivered measure, and this new Adjusted Monthly Distribution measure to the values field. This setup will provide a clear, side-by-side comparison of the original plan versus the dynamically adjusted plan for each contract over time.

 

Best regards,