Forum Discussion

Ilgar_Zarbali's avatar
Ilgar_Zarbali
Super User
1 year ago
Solved

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

I have created an Inventory Reconciliation report that includes a Year slicer. The report's main visual is a matrix with the following rows:

  • Opening Inventory
  • Purchases
  • Sales
  • Closing Inventory (Calculated)
  • On-Hand Quantity

The Closing Inventory (Calculated) is determined using this formula:
Closing Inventory (Calculated) = Opening Inventory + Purchases - Sales

The matrix has Months set on the Columns.

For example, if the year 2023 is selected:

  • January’s Opening Inventory is calculated using the following DAX formula:
Opening Inventory (Q) = 
CALCULATE(
    [On-Hand Qty],
    PREVIOUSMONTH('Calendar (On-Hand)'[Dates])
)
  • For February, the Opening Inventory should use January’s Closing Inventory (Calculated) or the previous month's Closing Inventory.

Currently, the Opening Inventory row uses its formula to calculate results, which might lead to inconsistencies. I believe the Opening Inventory should instead depend on the Closing Inventory (Calculated) from the previous month.

What would be your solution?

 

Custom Visuals
Dashboards
Data Modeling
Mobile
Reports
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    1 year ago

    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.  

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

     

    • Ilgar_Zarbali's avatar
      Ilgar_Zarbali
      Super User
      1 year ago

      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.  

       

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    Ilgar_Zarbali , You have to build Cummulative from start of time

     

    example

    Inventory / OnHand =
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),filter(all(date),date[date] <min(date[date]))) +
    CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


    Inventory / OnHand =
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),filter(all(date),date[date] <=max(date[date]))) +
    CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=max(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <=max(date[date])))

    Onhand BOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


    onhand EOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=Max(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <= Max(date[date])))

     

     

    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Ilgar_Zarbali 

     

    For beginning balances, I use something like this:

    CALCULATE (
    [inventory],
    FILTER (
        ALL ( calendartable ),
        calendartable[date] < MIN ( calendartable[date] )
    )
)

    It should be the running balance prior to the current row/column month.