Forum Discussion

svandamme's avatar
svandamme
Frequent Visitor
8 years ago
Solved

Stock Movements Calculation

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.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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