Calculation of monthly inventory level based on transactions

[ewakol]

Hello,

What i need: stock level on last day month, based on inventory issue transactions, by product and warehouse

What i have:  a list of transactions from day zero like you can see below

Need the most efficient way because the amount of data is extremely huge.

Houw could i extract it? I would appreciate for help.

[v-binbinyu-msft]

Hi @ewakol ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ewakol]

Hi @v-binbinyu-msft very thank you for reply and you time.

In SQL database table i have list of item warehouse transactions/movements, example for one item:

what i need is stock level on last month day (sometimes there is no movements in month), hope its more clear now

[v-binbinyu-msft]

Hi @ewakol ,

Please try to use visual calculate.

Please refer the attached .pbix file.

For more details, you could read related document:  Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ewakol]

You're briliant! Running sum its what i need! Your file was very helpfull, now i know how to add this value to table! Its great, but is it possible to add it to the chart also?

i tried to do it in two ways:

1. Using RUNNINGSUM function (DAX) - DAX | Microsoft Learn

but its look like my PBI dont know this function (i have Version: 2.128.952.0 64-bit (April 2024)):

2. Using Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

but... its impossible to use it with chart

Could You help me with that?

edit: when i try to add running sum to my table i see

😞

[v-binbinyu-msft]

Hi @ewakol ,

1. Visual calculation is a preview feature, please confrim you have select the option in Power BI Desktop.

2. This function can be used in visual calculations only.

3. You need select the visual in the report firstly, then click the New calculation to create calculations.

• Not all visual types are supported. Use the visual calculations edit mode to change visual type. Also, custom visuals haven't been tested with visual calculations or hidden fields.
• The following visual types and visual properties have been tested and found not to work with visual calculations or hidden fields:

         

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.