Forum Discussion

shannondean84's avatar
shannondean84
New Member
3 years ago
Solved

Unleashed - Sales Over Time

Hello all. 

I've got Power BI setup to pull data from Unleashed, our inventory management and sales software. 
I've got almost everything working and reporting the way I need it to but the last bit is a metric to measure our sales.

I'm looking to create a measure that will display how many of each item (product code) has been sold in the previous 4 months.

Would anyone have any idea on how to write that formula that can then be applied to a stock-on-hand table as a new column?

Happy to provide any data needed, i'm just not sure where to begin.

Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    shannondean84 , You need measure

     

    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

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


    Opening

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=Min(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=Min(date[date])))



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

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    shannondean84 , You need measure

     

    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

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


    Opening

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=Min(date[date]))) -
    CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=Min(date[date])))



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

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