Forum Discussion
Inventory Beginning and Ending Balance
Hi there,
I want to create a measure that shows Beginning Balance and Ending Balance of my Inventory.
I have 2 tables: Master Item and Inventory Transaction.
Here are my sample data:
Master Item
| Item No. |
| A |
| B |
| C |
Inventory Transaction
| Date | Item | Amount |
| 1-Jan-2021 | A | 1,200,000.00 |
| 1-Feb-2021 | A | (60,000.00) |
| 1-Mar-2021 | B | 300,000.00 |
| 1-Apr-2021 | B | (10,000.00) |
| 1-May-2021 | C | 500,000.00 |
| 1-Jun-2021 | A | 20,000.00 |
| 1-Jul-2021 | B | 30,000.00 |
| 1-Aug-2021 | C | (15,000.00) |
| 1-Sep-2021 | A | 24,000.00 |
| 1-Oct-2021 | B | (5,800.00) |
| 1-Nov-2021 | B | 30,000.00 |
| 1-Dec-2021 | C | 18,000.00 |
I also have slicer to filter my Date.
My expected output is like below table: (Date Filter = 01-Mar-2021 to 30-Nov-2021)
| Item | Beg Balance | Ending Balance |
| A | 1,140,000.00 | 1,184,000.00 |
| B | 0 | 344,200.00 |
| C | 0 | 485,000.00 |
Kindly please advise, how can I achive this.
Thank you.
6 Replies
- selimovd
Most Valuable Professional
Hey Olwin ,
that's possible with DAX measures:
Beg. Balance = CALCULATE( SUM( 'Inventory Transaction'[Amount] ), 'Inventory Transaction'[Date] = MIN( 'Inventory Transaction'[Date] ) )And for the end balence:
End Balance = CALCULATE( SUM( 'Inventory Transaction'[Amount] ), 'Inventory Transaction'[Date] = MAX( 'Inventory Transaction'[Date] ) )Be aware that you need a proper date table for the filtering to work. Here is a small tutorial how to create a date table:If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic Hi selimovd ,
I got this result using your DAX formula.
I think we need to total the amount for "Beg Balance" is < "First Date Filter" and "End Bal" is <= "End Date Filter
I also attach my pbix file.
Thank you.
- selimovd
Hey Olwin ,
in this case you have to change the posting date to equal the exact minimum date, like this:
BegBal = VAR first_date = [FirstDateVisible] RETURN CALCULATE( SUM( VE[Cost Amount] ), VE[Posting Date] = first_date )Also you have to make sure your the relationship between the Dates and the fact table has to be 1:n with single filter direction:
Then it should work like you want.
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHi selimovd ,
I still unable to get my expected result.
My founding is I need to create 1 more date table and not to relate it in any table, just to get the "Start Date" and "End Date" for all records with the same value on my date slicer.
Please find below link to find my revised pbix file.
I add new measure FirstDateVisibleNEW and LastDateVisibleNEW which refer to my new Date table. (You can see that this 2 measure is the same value as my date slicer).
Kindly please advise if there is any other solution to achive this 🙂
Thank you.
