Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Not applicable

Stock aging, counts and last transaction date of an item

Hi folks, 


I have an inventory model that consists mainly of item master and item transactions, with supporting tables such as transaction type, aging table and transaction with max last transaction date table. This max last date table contains item number, transaction type and the last transatction date, and ranks the item in descending order, so the last transaction appears on the top of the list with a rank of 1, and  so forth.


I want to report items, their last transaction date, their last transaction date for each transaction type and their age. Additionally, I want to report number of items (counts) and their age. If i drop Item numer on a table or matrix and then select Transaction Date from the max table, I get one row stating the Item Number and the Trnasaction Date. But when I drop Age from the Aging table, I get 3 rows, with same Item Number & Transaction Date but with different Age categories. Similarly, when I try to generate count of items in each age category, I am getting 3 age categories with an item ocunt of 1 for each item. 


I have prepared the model ( I am not sure what am I doing wrong, and now need a bit of guidance.



Microsoft Employee
Microsoft Employee



In Power BI, both table and matrix will aggregate fact values for each specific item automatically. However, in this scenario, since you have different Aging type associated with each items, once you drop Age into table/matrix, it will crossjoin different type of Age which returns multiple rows. So if you only want to get the data on item wise, you should't include Age into your table.



Not applicable

Thanks @v-sihou-msft for your reply. I understand the bit about cross join that comes into play when I include Age in the table. But the business wants to see Inventory Item, Last Transaction Date and Age at the same time. Any suggestions about how to shape the model to get this?




Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.