Total Stock at the end of each month

[LeoCata]

Hi, I have a table filled with information about inventory at a company, in it I have the values of the transactions with:

- item code (COD_ITEM);

- previous stock quantity (QTDE_ANTERIOR_ESTOQUE);

- transaction quantity (QTDE_OPERACAO_ESTOQUE);

- resulting stock (QTDE_SALDO_ESTOQUE);

- Unit prices (CUSTO_RESULTANTE);

- the date of the transaction (DATA);

- internal group code (COD_GRUPO_INTERNO).

And I want to create a visual that shows the inventory (monetarily)  at the end of each month for each group of parts.

One of the problems is that same parts can have a lot of transactions at the same day or has no record for a long time (even years), and I does not know how to make the visual understand that it is still in stock.

To better understanding, in this link you'll get a .CSV file with a sample of the data.

https://drive.google.com/open?id=1NT5dlEaJ5vEMZ4rzmeA8qm2KqvyO5DO3 

Can you help me?

I've tried a lot of functions, but the last was this, but it does not worked as well:

CLOSINGBALANCEMONTH(SUMX(BdCardex,BdCardex[CUSTO_RESULTANTE]*BdCardex[QTDE_SALDO_ESTOQUE]),BdCardex[DATA])

Thanks for your help.

[v-yuta-msft]

@LeoCata ,

Hi, I have a table filled with information about inventory at a company, in it I have the values of the transactions with:

- item code (COD_ITEM);

- previous stock quantity (QTDE_ANTERIOR_ESTOQUE);

- transaction quantity (QTDE_OPERACAO_ESTOQUE);

- resulting stock (QTDE_SALDO_ESTOQUE);

- Unit prices (CUSTO_RESULTANTE);

- the date of the transaction (DATA);

- internal group code (COD_GRUPO_INTERNO).

And I want to create a visual that shows the inventory (monetarily)  at the end of each month for each group of parts.

One of the problems is that same parts can have a lot of transactions at the same day or has no record for a long time (even years), and I does not know how to make the visual understand that it is still in stock.

---

Could you please show the expected result also?

Regards,

Jimmy Tao

[LeoCata]

I'd like two visuals, one with de total amout of stock at the endo of the year or month, in bars, like this:

https://drive.google.com/open?id=1ehhXNkP6q2FO_-lHphCyEBgAG9preUPy 

And one showing the % variance betwen month of diferent years and one month over the previous one, like this:

https://drive.google.com/open?id=1_x7uVGTv6p5IhCjkHwsAbBiOGG8525K1 

[amitchandak]

@LeoCata 

I tried to look at the file. file. It is all mixed up.

You can rebuild inventory from start to time for any day like this

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

Try like

Cumm Purcahse = CALCULATE(SUM(Purcahse[Sales qty]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales qty]),filter(date,date[date] <=max(date,date[date])))

Final Inventory = [Intial Inventory] +[Cumm Purcahse ] -[Cumm Sales]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[LeoCata]

Hi, thanks for your help, I've tried your suggestions, but it did not work.

I've created two new columns in the table, one with the sales total and another with the purchase total.

With that done, I've tried this:

cumm sales = 

CALCULATE(SUM(BdCardex[Entradas]),filter(BdCardex,BdCardex[DATA] <=maxx(BdCardex,BdCardex[DATA])))

cumm purchase = CALCULATE(SUM(BdCardex[Entradas]),filter(BdCardex,BdCardex[DATA] <=maxx(BdCardex,BdCardex[DATA])))

Then, as I don't have the initial inventory, the table has it as a purchase at the beginning of it, the final calculation was:

Final Inventory = [cumm purchase]-[cumm sales]

The result has been for each month, it shows the difference between purchase and sales, but not accumulated.

https://drive.google.com/open?id=1oe4KDt_Zf8Byw3S0V_Q6ox8g2Eto7rR6