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 sales from mi compañy to resellers

Sales Out: the sales from the resellers to end users

Initial stock : the stock on hand reported by the resellers at 01/01/2020 

 

I need to calculate the stock by  adding the sell in and sustracting the sell out : 

 

Example : 

 

 

initial stock  productA in reseller A =10000 to that value i have to add the sell in and sustract the sell out 

 

01/01/2020 stock=10000

sales in jan/2020=5000

Sales out in jan/2020=7000

stock in feb/2020= 10000+5000-7000=8000<then , this should be the initial stock in february so :

sales in feb/2020=2000

sales out feb/2020=5000

stock in march/2020=8000+2000-5000=5000 , and so on 

 

i have a sales in table with dates and sku and reseller number , also i have a sales out table with the same dimentions , calendar table , and product table

 

TY!!!

 

DAX

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Please place a link to a model with your tables. Just anonymize the data that can't be exposed. I don't want to create the tables you're referring to by hand...

    Best
    D
  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    10 months ago

    Model setup

    1. Create a Date table that covers the full range of your data and mark it as a date table.
    2. Relate Date to Sales In by the date column and to Sales Out by the date column. Use single direction from Date to each fact.
    3. Relate Product to all three tables. Relate Reseller to all three tables.
    4. Initial Stock table holds one row per reseller and product with the quantity on 2020 01 01.

    Measures

    Sales In Qty =
    SUM('Sales In'[Qty])

    Sales Out Qty =
    SUM('Sales Out'[Qty])

    Net Movement =
    [Sales In Qty] + [Sales Out Qty] * COS(PI())

    Initial Stock Qty =
    COALESCE(SUM('Initial Stock'[Qty]), 0)

    Cumulative Net Movement since 2020 01 01 =
    VAR Anchor = DATE(2020, 1, 1)
    RETURN
    CALCULATE(
    [Net Movement],
    FILTER(
    ALLSELECTED('Date'[Date]),
    'Date'[Date] >= Anchor && 'Date'[Date] <= MAX('Date'[Date])
    )
    )

    Stock On Hand by date =
    [Initial Stock Qty] + [Cumulative Net Movement since 2020 01 01]

    Stock at month end for visuals grouped by month =
    VAR LastDay = EOMONTH(MAX('Date'[Date]), 0)
    RETURN
    CALCULATE(
    [Stock On Hand by date],
    KEEPFILTERS('Date'[Date] = LastDay)
    )

     

  • 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!