Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

stock calculation

Hello  , i have a simple problem but i cant find a simple answer : I tried to look on older posts but i couldnt find a solution to mi problem :    I have a model where i have :  Sales in : the sa...
DAX
Olufemi7's avatar
Olufemi7
Icon for Super User rankSuper User
10 months ago

You can calculate monthly or daily stock balances in Power BI using cumulative DAX measures.

Model Assumption:

  • SalesIn → stock received

  • SalesOut → stock issued

  • InitialStock → opening quantity

  • Calendar → connected to both SalesIn and SalesOut tables by Date
    Base Measures

  •  

Total Sales In = SUM(SalesIn[Quantity])
Total Sales Out = SUM(SalesOut[Quantity])
Initial Stock = SUM(InitialStock[InitialStock])

Cumulative and Stock

Stock =
[Initial Stock] +
CALCULATE([Total Sales In], FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date]))) -
CALCULATE([Total Sales Out], FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date])))

 

➤ Create a calculated table

Go to Model view → New Table, then paste:

ExampleStock =
DATATABLE(
    "Month", STRING,
    "MonthOrder", INTEGER,
    "Initial", INTEGER,
    "Sales In", INTEGER,
    "Sales Out", INTEGER,
    "Stock", INTEGER,
    {
        {"Jan", 1, 10000, 5000, 7000, 8000},
        {"Feb", 2, 0, 2000, 5000, 5000},
        {"Mar", 3, 0, 3000, 1000, 7000}
    }
)

 

Hope this helps!