Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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
Thank you! I'll give it a try.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
105 | |
88 | |
75 | |
67 |
User | Count |
---|---|
123 | |
112 | |
95 | |
83 | |
73 |