Inventory Beginning and Ending Balance

[Olwin]

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.

[Olwin]

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.

"https://smesolution-my.sharepoint.com/:u:/g/personal/olwin_smesolution_onmicrosoft_com/EU-gqB28B2lNn... 

[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 regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Olwin]

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.

https://smesolution-my.sharepoint.com/:u:/g/personal/olwin_smesolution_onmicrosoft_com/EYZh623QdylBl... 

[selimovd]

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.

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 regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Olwin]

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

[selimovd]

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:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

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 regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic