Forum Discussion
stock ageing
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 )
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.
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 likeAgingDays = 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.
7 Replies
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 KumarHi 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 slicerDate 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 quantityIf 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.
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?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.
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 likeAgingDays = 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.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
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
