How to Calculate Stock Aging Using FIFO Method

[RAJASEKAR-O]

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
but 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

[grazitti_sapna · Accepted solution]

Hi @RAJASEKAR-O,

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!

[v-dineshya]

Hi @RAJASEKAR-O ,

Thank you for reaching out to the Microsoft Community Forum.

Could you please try the proposed solution shared by @grazitti_sapna  ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @RAJASEKAR-O ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @RAJASEKAR-O ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh