How to Calculate Stock Aging Using FIFO Method

[rajasekaro]

hi Team,
I’m working on a stock/ inventory aging report in Power BI and need to apply the
FIFO (First-In-First-Out) logic to calculate the age of the remaining stock.
 i tryd but not comming correctly ,it showing stock movement

How to Calculate Stock Aging Using FIFO Method
table : F_Inventory

Item	DOCID	IN_DOCDATE	qty	TRTY
Surgical Mask	450149	30-01-2024	400	RCPT
Surgical Mask	450169	10-03-2024	400	RCPT
Surgical Mask	450195	08-08-2024	250	RCPT
Surgical Mask	450239	07-06-2024	100	RCPT
Surgical Mask	450308	23-07-2025	100	ISSU
Surgical Mask	450310	08-09-2024	250	ISSU
Surgical Mask	450314	01-01-2025	250	RCPT
Surgical Mask	450331	31-05-2024	100	RCPT
Surgical Mask	450402	30-01-2024	200	RCPT
Surgical Mask	450415	25-04-2024	200	RCPT
Surgical Mask	450420	01-03-2024	150	RCPT
Surgical Mask	450443	05-05-2025	100	RCPT
Surgical Mask	450551	30-01-2025	300	RCPT
Surgical Mask	450584	22-02-2024	150	RCPT
Surgical Mask	450589	04-02-2024	100	RCPT
Surgical Mask	450593	01-02-2024	400	RCPT
Surgical Mask	450605	25-04-2024	300	RCPT
Surgical Mask	450629	28-01-2024	100	RCPT
Surgical Mask	450635	03-07-2024	150	RCPT
Surgical Mask	450897	12-04-2024	300	RCPT
Surgical Mask	450927	21-05-2024	300	ISSU
Surgical Mask	450947	06-05-2025	150	RCPT
Surgical Mask	450965	04-04-2024	300	RCPT
Surgical Mask	450975	05-03-2024	150	ISSU
Surgical Mask	450995	06-06-2025	250	RCPT

[v-sshirivolu]

Hi @rajasekaro ,

I would also take a moment to thank @grazitti_sapna , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-sshirivolu]

Hi @rajasekaro ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[grazitti_sapna · Accepted solution]

Hi @rajasekaro,

Create a measure for running balance for receipts

CumulativeReceipts =
CALCULATE(
SUM(F_Inventory[qty]),
FILTER(
F_Inventory,
F_Inventory[Item] = EARLIER(F_Inventory[Item]) &&
F_Inventory[TRTY] = "RCPT" &&
F_Inventory[IN_DOCDATE] <= EARLIER(F_Inventory[IN_DOCDATE])
)
)

Dax for running balance for issues

CumulativeIssues =
CALCULATE(
SUM(F_Inventory[qty]),
FILTER(
F_Inventory,
F_Inventory[Item] = EARLIER(F_Inventory[Item]) &&
F_Inventory[TRTY] = "ISSU" &&
F_Inventory[IN_DOCDATE] <= EARLIER(F_Inventory[IN_DOCDATE])
)
)

DAX for Remaining stock per receipt

RemainingQty =
VAR Receipts = F_Inventory[qty]
VAR Used = CALCULATE(
SUM(F_Inventory[qty]),
FILTER(
F_Inventory,
F_Inventory[Item] = EARLIER(F_Inventory[Item]) &&
F_Inventory[TRTY] = "ISSU" &&
F_Inventory[IN_DOCDATE] >= EARLIER(F_Inventory[IN_DOCDATE])
)
)
RETURN MAX(0, Receipts - Used)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!