Dynamic Inventory Reconciliation: Aligning Opening and Closing Balances in Power BI

Hi Ilgar_Zarbali ,

Here I create a sample to have a test. I think you can try code as below to create a measure.

Opening Inventory =
VAR _Opening_Inventory_forJan =
    CALCULATE (
        [On-Hand Qty],
        FILTER (
            ALL ( 'Calendar (On-Hand)' ),
            'Calendar (On-Hand)'[Date]
                <= EOMONTH (
                    MAX ( 'Calendar (On-Hand)'[Date] ),
                    - MAX ( 'Calendar (On-Hand)'[Month] )
                )
                && 'Calendar (On-Hand)'[Date]
                    >= EOMONTH (
                        MAX ( 'Calendar (On-Hand)'[Date] ),
                        - MAX ( 'Calendar (On-Hand)'[Month] ) - 1
                    ) + 1
        )
    )
VAR _PUR =
    CALCULATE (
        [Purchase (Q)],
        FILTER (
            ALLSELECTED ( 'Calendar (On-Hand)' ),
            'Calendar (On-Hand)'[Date] <= EOMONTH ( MAX ( 'Calendar (On-Hand)'[Date] ), -1 )
        )
    )
VAR _Sales =
    CALCULATE (
        [Sales (Q)],
        FILTER (
            ALLSELECTED ( 'Calendar (On-Hand)' ),
            'Calendar (On-Hand)'[Date] <= EOMONTH ( MAX ( 'Calendar (On-Hand)'[Date] ), -1 )
        )
    )
RETURN
    _Opening_Inventory_forJan + _PUR - _Sales

Result is as below.

You can download my sample file to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you all for your soluitions. I found v-rzhou-msft

solution interesting. Actually, I have used other dax formulas. I will try this also.