stock ageing

[rajasekaro]

hi team, i have inventory table , need to calculate Aging 

DOCDATE	ITEMID	QTY	PLUSORMINUS	BRANCHID	LOCID	STOCKVALUE
24-11-2024	ITEM001	35	p	BRANCH3	LOC2	1247.4
16-09-2024	ITEM010	25	m	BRANCH3	LOC2	1422.41
26-07-2024	ITEM011	64	m	BRANCH3	LOC3	2774.7
16-07-2024	ITEM009	52	m	BRANCH3	LOC1	1488.85
07-06-2024	ITEM008	91	p	BRANCH1	LOC1	3458.4
18-02-2025	ITEM020	44	m	BRANCH1	LOC5	3906.58
0 6-07-2024	ITEM012	18	m	BRANCH2	LOC5	180.85

need to calculate Stock Ageing 
dynamic date fillter need 
slicer (Branch, location,date )

[FarhanJeelani · Accepted solution]

Hi @rajasekaro ,

For the aging bucket, you can either create separate measures to display in different columns within a table visual, or use the code below to create a single measure and place it in the columns of a matrix visual.

AgingBracket = VAR RefDate = SELECTEDVALUE('Date'[Date], TODAY()) VAR DocDate = MAX('Inventory'[DOCDATE]) -- use the current row/group context VAR AgeDays = DATEDIFF(DocDate, RefDate, DAY)

RETURN SWITCH( TRUE(), AgeDays < 0, "Not due yet", AgeDays <= 30, "0-30", AgeDays <= 60, "31-60", AgeDays <= 90, "61-90", "91+" )

Please mark this post as solution if it helps you. Appreciate Kudos.

[v-pgoloju]

Hi @rajasekaro,

Just following up to see if the Response provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @rajasekaro,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @rajasekaro,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @FarhanJeelani and @Poojara_D12  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Poojara_D12 · Accepted solution]

Hi @rajasekaro 

To calculate stock aging in Power BI from your inventory table, you first need to establish how long each item has been in stock relative to a dynamic reference date, which should come from a date slicer. Typically, aging is calculated as the difference in days between the document date (DOCDATE) and either today’s date (TODAY()) or a user-selected date from a slicer. You can create a calculated column like AgingDays = DATEDIFF(Results[DOCDATE], SELECTEDVALUE('Date'[Date], TODAY()), DAY) to get the number of days in stock for each record. Then, to make the aging analysis meaningful, you usually group these days into aging buckets (for example: 0–30, 31–60, 61–90, 90+), which you can implement with a calculated column or a SWITCH statement. Once this is set up, you can create measures such as total quantity or stock value within each aging bucket. With slicers on Branch, Location, and Date, the report becomes dynamic—users can filter by any branch or location, pick a reference date, and immediately see how stock quantities and values are distributed across the defined aging buckets. This gives a flexible and interactive view of stock aging in your dashboard.

[FarhanJeelani]

Hi @rajasekaro ,

Age in days = reference_date - DOCDATE

Use a dynamic reference_date from a date slicer
Optional: categorize into aging buckets (0-30, 31-60, 61-90, 91+)
Keep Branch and Location slicers; they filter the results automatically

Assuming:

Your table is Inventory with columns: DOCDATE, ITEMID, QTY, PLUSORMINUS, BRANCHID, LOCID, STOCKVALUE
DOCDATE is a date (dd-MM-yyyy format in your sample)
You have a Date table (Date) with a column Date[Date] to drive the slicer

Date reference for aging (dynamic)
Create measures (don’t use a fixed date in a calc column; use a measure so it responds to the slicer)
ReferenceDate : = SELECTEDVALUE('Date'[Date], TODAY())

Aging by buckets (values are in STOCKVALUE; you can swap to QTY if you prefer)

0-30 days Aging_0_30_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) >= 0 && DATEDIFF(Inventory[DOCDATE], rd, DAY) <= 30 ) )

31-60 days Aging_31_60_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) > 30 && DATEDIFF(Inventory[DOCDATE], rd, DAY) <= 60 ) )

61-90 days Aging_61_90_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) > 60 && DATEDIFF(Inventory[DOCDATE], rd, DAY) <= 90 ) )

91+ days Aging_91Plus_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) > 90 ) )

Build the visuals
Add slicers:
Date slicer using Date[Date] (this sets the ReferenceDate)
Branch slicer using Inventory[BRANCHID]
Location slicer using Inventory[LOCID]

Create a matrix or table:
Rows: BRANCHID (or you can place LOCID as rows or both with a nested matrix)
Columns: Aging buckets (you can show as separate columns)

Values: the four measures above (Aging_0_30_Value, Aging_31_60_Value, Aging_61_90_Value, Aging_91Plus_Value)
If you’d prefer a single “bucket label” column, you’d typically need a static bucket dimension; with a dynamic reference date, keeping four separate measures is the simplest path.

Optional: weighted aging by quantity

If you want average aging days weighted by quantity (instead of stock value), you can add: Weighted_AgeDays := VAR rd = [ReferenceDate] RETURN SUMX(Inventory, Inventory[QTY] * DATEDIFF(Inventory[DOCDATE], rd, DAY)) / SUM(Inventory[QTY])

Pleae note:

Use ALLSELECTED or ALLEXCEPT in the CALCULATE filters if your report uses multiple unrelated slicers and you want to preserve certain filters. For example: FILTER(Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) >= 0 && DATEDIFF(Inventory[DOCDATE], rd, DAY) <= 30) This respects the current slicers (BRANCHID, LOCID) without forcing the filter off.

Please mark this post as solution if it helps you. Appreciate Kudos.

[rajasekaro]

0-30 days Aging_0_30_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) >= 0 && DATEDIFF(Inventory[DOCDATE], rd, DAY) <= 30 ) )

31-60 days Aging_31_60_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) > 30 && DATEDIFF(Inventory[DOCDATE], rd, DAY) <= 60 ) )

61-90 days Aging_61_90_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) > 60 && DATEDIFF(Inventory[DOCDATE], rd, DAY) <= 90 ) )

91+ days Aging_91Plus_Value := VAR rd = [ReferenceDate] RETURN CALCULATE( SUM(Inventory[STOCKVALUE]), FILTER( Inventory, DATEDIFF(Inventory[DOCDATE], rd, DAY) > 90 ) )

this are all Aging bucket? 

why we need to calculate every one in saprate?

[FarhanJeelani · Accepted solution]

Hi @rajasekaro ,

For the aging bucket, you can either create separate measures to display in different columns within a table visual, or use the code below to create a single measure and place it in the columns of a matrix visual.

AgingBracket = VAR RefDate = SELECTEDVALUE('Date'[Date], TODAY()) VAR DocDate = MAX('Inventory'[DOCDATE]) -- use the current row/group context VAR AgeDays = DATEDIFF(DocDate, RefDate, DAY)

RETURN SWITCH( TRUE(), AgeDays < 0, "Not due yet", AgeDays <= 30, "0-30", AgeDays <= 60, "31-60", AgeDays <= 90, "61-90", "91+" )

Please mark this post as solution if it helps you. Appreciate Kudos.