I need your help with a DAX problem. I have a table named fItem Transactions, this is a fact table that contains the movement of inventory (items). So everytime a sale, purchase, adjustment etc. is made, the transaction is reflected in a row along with ending balance as of that date. Please note that for each site (warehouse) - a different transaction will be made and ending balance will be different for each site.
I wasn’t able to attach the pbix file to this post - not sure how to do that.
screenshot of fItem Transactions table (see microsoft community post at the end):
What I want to achieve:
I want to find out the inventory balance as of each day. You can see screenshot in Microsoft link shared - this output is fine (it’s just the numbers are wrong as they are the latest transaction only and not the sum of all sites latest transaction)
What I could get to so far:
I was able to put together a DAX measure (see below) that helped me take the latest transaction for an item and extrapolate it into the future dates. But actually - I want to take the sum of latest transaction for each site and then extrapolate that into the future (not just latest transaction). If i were to add the site in row into a matrix table, then the result is correct as the formula automatically gets filtered by site. But when site field is removed from rows, the below DAX takes the latest transaction for the whole item only. Please note that in the below DAX, I was not able to extrapolate the item_transaction_id into the future. The reason i want to do that is sometimes, for a particular date, there would be multiple transactions, and the only way to find out the latest transaction is by finding out the highest item_transaction_id for that date.
Optional: I would also like to be able to just take out the item number from the row and the total value should be correct as that would be the total inventory balance of all items.
Optional: I would like the inventory balance to be only shown till current date, at the moment in the below DAX you can see that the inventory balance is shown till Dec 2023 - basically the max available date in my calendar table.
Inventory Balance =
VAR Max_Period_Date = MAX(dCalendar[Date])
(variable not created) VAR Latest_Transaction - need to add a condition such that the highest (max) of item_transaction_id is considered for a particular date as some dates can have multiple transactions so I want to take the last transaction only
VAR Last_Balance_Date = CALCULATE(MAX(‘fItem Transactions’[Item Transaction Date]),dCalendar[Date]<=Max_Period_Date) RETURN
CALCULATE(MAX(‘fItem Transactions’[Inventory Balance]),dCalendar[Date]=Last_Balance_Date)
I wasn’t able to upload the pictures, so here is a post with pictures as well. ( Sum of max of two categories extrapolated into fut… - Microsoft Power BI Community)
DAX Help Needed.pbix (741.8 KB)