Forum Discussion
stock ageing
| 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 |
hi team i have stock data
I need to create Stock ageing report table like this
| 1-30days | 31-60days | 61-90days | 91-120 days | 121-150 days | above 180days | Closeing qty | |
| itemA | 30 | 30 | |||||
| Item B | 45 | 30 | 75 |
Hi rajasekaro
I hope I've correctly interpreted your data and the desired outcome. For validation purposes, I used November 24, 2024 (11/24/2024) as a reference date. However, for your actual implementation, I recommend either:
Using TODAY() for real-time analysis, or
Implementing a date table for dynamic period calculations.
Required Calculated Columns:
DaysInStock = VAR ReportDate = DATE(2024, 11, 30) // Fixed reporting date RETURN DATEDIFF(StockTransactions[DOCDATE], ReportDate, DAY) AgeBucket = SWITCH( TRUE(), [DaysInStock] <= 30, "0-30 days", [DaysInStock] <= 60, "31-60 days", [DaysInStock] <= 90, "61-90 days", [DaysInStock] <= 120, "91-120 days", "Above 120 days" )
Measure:
Adjusted Qty = VAR Sign = SWITCH( SELECTEDVALUE(StockTransactions[PLUSORMINUS]), "p", 1, // Positive for incoming stock "m", -1, // Negative for outgoing stock 0 // Default case ) RETURN SUM(StockTransactions[QTY]) * Sign
6 Replies
Hi rajasekaro ,
For a Power BI solution, you would embed the ageing logic directly into your data model using DAX. This creates a dynamic and repeatable report. The primary method involves adding calculated columns to your main data table, which we can assume is named 'StockData'.
Your first step is to create a column that calculates the age in days for each transaction row. This is achieved with a DAX formula that subtracts the transaction's DOCDATE from the current date. The INT function ensures you get a whole number representing the days.
// DAX Calculated Column: Age (Days) Age (Days) = INT(TODAY() - 'StockData'[DOCDATE])With the age of each item calculated, you can now add a second column to categorize these ages into the desired buckets. The SWITCH(TRUE(), ...) function in DAX is ideal for this, as it evaluates each condition in order until one is met. The numeric prefixes like "01." and "02." are a helpful technique within Power BI to ensure that your text-based buckets will sort correctly when used as columns in a visual, rather than sorting alphabetically.
// DAX Calculated Column: Ageing Bucket Ageing Bucket = VAR CurrentAge = 'StockData'[Age (Days)] RETURN SWITCH ( TRUE (), CurrentAge <= 30, "01. (1-30 days)", CurrentAge <= 60, "02. (31-60 days)", CurrentAge <= 90, "03. (61-90 days)", CurrentAge <= 120, "04. (91-120 days)", CurrentAge <= 150, "05. (121-150 days)", CurrentAge <= 180, "06. (151-180 days)", "07. (Above 180 days)" )After adding these two calculated columns to your StockData table, you can build the final report visualization. You would use a Matrix visual in your Power BI report canvas and configure it by dragging your data fields to the appropriate areas: place the ITEMID field in the 'Rows' section, your new Ageing Bucket field in the 'Columns' section, and the QTY field into the 'Values' section. Power BI will then automatically generate the interactive stock ageing report based on this structure.
Best regards,
Hi rajasekaro
I hope I've correctly interpreted your data and the desired outcome. For validation purposes, I used November 24, 2024 (11/24/2024) as a reference date. However, for your actual implementation, I recommend either:
Using TODAY() for real-time analysis, or
Implementing a date table for dynamic period calculations.
Required Calculated Columns:
DaysInStock = VAR ReportDate = DATE(2024, 11, 30) // Fixed reporting date RETURN DATEDIFF(StockTransactions[DOCDATE], ReportDate, DAY) AgeBucket = SWITCH( TRUE(), [DaysInStock] <= 30, "0-30 days", [DaysInStock] <= 60, "31-60 days", [DaysInStock] <= 90, "61-90 days", [DaysInStock] <= 120, "91-120 days", "Above 120 days" )
Measure:
Adjusted Qty = VAR Sign = SWITCH( SELECTEDVALUE(StockTransactions[PLUSORMINUS]), "p", 1, // Positive for incoming stock "m", -1, // Negative for outgoing stock 0 // Default case ) RETURN SUM(StockTransactions[QTY]) * Sign
Hi rajasekaro
I wanted to check in regarding your question about to create Stock ageing report table
Do you have any follow-up questions, or is there anything else you’d like help with? It would be great to hear how things are going on your end and if you’ve run into any challenges along the way.
Your feedback helps us make sure you get the right support.If your issue has already been resolved, please consider marking the response as the accepted answer this can also help others who might be facing the same situation.
Thank you, and I look forward to your update!
Hi rajasekaro
Just wanted to check in and see if the suggestions shared earlier by the Elena_Kalina DataNinja777 helped you build the stock ageing report you were working on.
If you still have questions or need any additional help, please provide the sample data with expected output..
we’re here and happy to assist!
Thank you again for being part of the community!
Hi,
How did you arrive at Table 2 from Table 1????? Based on Table 1. show the expected result very clearly.
