Inventory Balance /Out Standing

Frankee · October 23, 2021, 2:45am

Hello All, need to show a few measures with
Inventory Out (pieces on rent)
Inventory In (returned pieces)
Inventory Balance (during the selected date)

I’ve spend a few days on this, I cant get it. I’ve a attached the sample excel file including the sample if what is needed.

RECON-SHEET102221.xlsx (774.3 KB)

Thanks for your time in advance,

Frank

Anurag · October 24, 2021, 7:01am

Hi,

Thanks for posting your query in the form it will be very helpful for me or other if you upload pbix file which in progress so we will try to help you in that instead of creating everything it will save time and your query can be resolved quickly.

Thanks,
Anurag

EnterpriseDNA · October 24, 2021, 11:26pm

Hello @Frankee. We hope that you’ll also upload your PBIX file so other users and experts may help you more.

Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

Frankee · October 25, 2021, 2:39pm

i just completed the power bi version. thank you for the suggestion.

we I have four tables.

Calendar
Material Type w/ Price
Inventory Out on Rent w/ a date…just a list of Material w/ Quantity
Inventory In - off rent material w/ return date

-Need to show the starting inventory qty and value $.
-Next need to show the inventory In (returned)
-Would like to show the subtraction of the material qty and value.
-starting and ending inventory.

MatInventory.pbix (136.3 KB)

EnterpriseDNA · October 26, 2021, 11:30pm

Thank you for providing the PBIX file.

Bumping this post for more visibility.

ankit · October 30, 2021, 8:38am

Hi @Frankee - Please explain the issue you are facing and what help is exactly needed. If related to Inventory Out then there is no date level information available and hence giving sum of all.

Thanks
Ankit J

EnterpriseDNA · November 1, 2021, 12:22am

Hello @Frankee, following up what @ankit requested above.

A perfect initial question includes all of the following:

A clear explanation of the problem you are experiencing
A mockup of the results you want to achieve
Your current work-in-progress PBIX file
Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Frankee · November 1, 2021, 3:45pm

I found the solution I was looking for here:
Qty monthly balance based on initial inventory

Starting Balance had no date.
As inventory came in w/ dates , needed the qts. subtracted from the starting balance.
Required month end balance for each month.

Here are the measures used. (see pics)

BalanceStart = [Balance]+[InventoryIn]

InventoryIn = SUMX(InventoryIn,
InventoryIn[BYardCount]
)

Balance =
VAR DD=EOMONTH(LASTDATE(‘Calendar’[Date]),0)
VAR SumAllitemsinMonth=CALCULATE(SUM(InventoryIn[BYardCount]),ALL(InventoryOut))
RETURN

IF(SumAllitemsinMonth=0,BLANK(),
SUM(InventoryOut[Pieces])- CALCULATE(sum(InventoryIn[BYardCount]),FILTER(ALL(‘Calendar’),‘Calendar’[Date]<=DD)))

two views required: