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 dat...
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

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