Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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.
Hi @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.
Hey @Olwin ,
what exactly is not working?
You don't need a second date table that is not connected to any other tables. As I mentioned in the last reply, you have to set the filter direction between date table and VE table to "single". Then the report is working properly.
Hi @selimovd ,
The result is not the same as my initial expectation.
Herewith I send you my .pbix file. I have followed your guidance to set filter direction to "single". Kindly please take a look.
Sample pbix file
Thank you.
Best regards,
Olwin
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] )
)
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |