STOCK FLOW

[Anonymous]

Hello everyone, how are you?

I develop a project whose objective is to calculate the stock of certain customers using 3 tables.

fPurchases, fSales and fStock

I carried out some measures to return the balance between fPurchases and fSales, with this balance accumulated in the Closing measure and sent to the context of the next month in the Inventory of the Month measure.

Closing =
CALCULATE(
    [Purchases-Sales],
    FILTER(
        ALL(dCalendar[Date]),
        dCalendar[Date] <= MAX(dCalendar[Date])
    )
)

Inventory of the Month = CALCULATE([Closing], DATEADD(dCalendario[Date], -1, MONTH))

In other words, while purchases and sales occur throughout the month, the balance of this account will be determined until the last day of the filter context in the Closing measure, while the Inventory of the Month measure will transfer this result to the first day of the next month.

The purchase and sales tables do not capture complete information, and it is not possible to rely solely on them to calculate inventory.

This way, we have the fEstoque table with real stock data that is updated monthly, where, in most cases, it has different values ​​than the results of my measurements. The intention is to have the measurements I created adjusted by the difference between the stock that was calculated and the stock that was entered in fEstoque.

Ex: IF( [Stock of the Month] <> SUM(fStock[quantity], SUM(fStock[quantity], [Stock of the Month] )

A simple IF or SWITCH does not work, because in the following month the calculation stops considering SUM(fEstoque[quantidade] as the initial stock for the month.

Thank you very much to anyone who can give me some advice!

[amitchandak · Accepted solution]

@Anonymous , You have try measures like

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])))

If need add purcahse and sales

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])))

[Anonymous]

Hello friend

This way the same problem occurs. After a new load of data, with new months, the measure no longer considers the stock adjusted for the difference. I believe I need to create something like a loop.

But thanks