Blueprint for inventory/balance reporting

Power BI
1

Power BI is a great tool to total transactions in a given period, e.g. items sold in month January 2019, revenue in year 2019, etc. But what to do if I want to know the current balance or inventory level, like “there is $13.753,28 on the account” or “there are 27 game boxes on stock”?
I basically see there options:

  1. Get the current balance value at each refresh from the data source.
  2. Sum up all transactions since forever, which, if I have access to all this data at all, can be a lot of data.
  3. Have a starting balance “pseudo” transaction that gives me the balance e.g. at January 1st and then calculate from there.

I checked the finacial reporting and inventory managment showcases and both use approach number 1. Is this the solution I should be looking for? What are pros and cons for this approach and are there showcases using the other approaches as well?

Thanks a lot in advance!
Martin

2

Hi Martin,

There could be a possible fourth option to consider and that’s using aggregations.

1 Like
3

Hi Melissa,

thank you for the content. I also found an additional solution: There is a DAX function OPENINGBALANCEYEAR which seems to be kind of an implementation of concept 2, given, of course, you have complete transaction data, at least summarized or grouped, from the very beginning.

Is there a showcase on Enterprise DNA that demonstrates the OPENINGBALANCEYEAR function?

Best regards,
Martin

4

Hi Martin,

Here’s a link to the Knowedge Base.
https://info.enterprisedna.co/dax-function-guide/openingbalanceyear/

I hope this is helpful.

1 Like
5

I hope below video will be helpful in calculating the stocks on hand

1 Like
6

Thank you @Mohammed_ali
This is pretty much what I was looking for!
Best regards,
Martin

7

You’re welcome Martin :grinning: