Stock Movements Calculation

[svandamme]

Hi,

I have a stock movements table that contains stock movements for the dates there is a stock movement, so not all the dates are in the table. I want to create a report that shows for every date the stock at that moment. So if there is a stock movement on jan 1 and jan 3, I want to be able to see the stock on jan 2 also.

I have already created a formula and it works on row level but on Grand Total level it is wrong.

The formula is the following one:

Qty:=SUMX (
VALUES ( ‘ITEM_VARIANT'[PK_Item_Variant] ),
CALCULATE (
SUM (F_STOCK_MOVEMENTS[M_Stock_Running_Qty] ),
CALCULATETABLE (
LASTNONBLANK (
‘DATE'[Calendar_Date],
CALCULATE ( COUNTROWS ( F_STOCK_MOVEMENTS ) )
),
DATESBETWEEN (
‘DATE'[Calendar_Date],
BLANK (),
MAX ( ‘DATE'[Calendar_Date] )
))))

I have solved this another way by creating in SQL a table but then I go from 10M rows to 750M rows. It works and it is performant but I would prefer to solve it with a measure. I think I am close but I just cannot figure out how to get the grand total correct.

I have put the file on Google Drive:

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

If someone has any suggestions, please let me know. 

Thanks in advance.

[Anonymous]

Hi @svandamme,

Please share original table data for test and coding format. Current power bi is not suitable to analytics pivoted excel workbook sheet.

According to your description, it seems like normal running total calculation, you can refer to below link to know more about these.

Running Total Techniques in DAX

Regards,

Xiaoxin Sheng

[svandamme]

Link to Power BI file:

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

[Anonymous · Accepted solution]

Hi @svandamme,

Maybe you can try to use below formula:

Measure = 
var temp=CALCULATE (
    SUM ( F_STOCK_MOVEMENTS[M_Stock_Movement_Qty] ),
    FILTER (
        ALLSELECTED ( F_STOCK_MOVEMENTS ),
        [FK_Date] <= MAX ( F_STOCK_MOVEMENTS[FK_Date])
    ),
    VALUES ( F_STOCK_MOVEMENTS[BK_Item_Code] ),
    VALUES ( F_STOCK_MOVEMENTS[BK_Location_Code] )
)
return
if(temp= BLANK(),[Stock],temp)

My formula can't replace previous blank records, so I use 'stock' measure to replace blank part.

Regards,

Xiaoxin Sheng