Forum Discussion

redhol's avatar
redhol
Frequent Visitor
8 years ago
Solved

Weekly Inventory through Inventory movements column

Hi All,   Might be an easy one but I have yet to figure it. I am trying to display closing stock for each week for some inventory analysis that I am working on - I am struggling to get this value a...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi redhol,

     

    The cause could be the [Date] in the dates. Please try the formula below.

    Rolling Stock =
CALCULATE (
    SUM ( HR_StockMovements[MovementQty] ),
    FILTER (
        ALL ( 'Calculations'[Date]),
        'Calculations'[Date] <= MAX ( 'Calculations'[Date] )
    )
)

    Rolling Stock =
CALCULATE (
    SUM ( HR_StockMovements[MovementQty] ),
    FILTER (
        ALL ( 'Calculations'[Date].[Date] ),
        'Calculations'[Date].[Date] <= MAX ( 'Calculations'[Date].[Date] )
    )
)

    Best Regards,

    Dale

redhol's avatar
redhol
Frequent Visitor
8 years ago

Hi Dale,

 

THanks for the reply - data for stock movements is shown below

 

And current formula is

 

Rolling Stock =
CALCULATE(
Sum(HR_StockMovements[MovementQty]),
Filter(
ALL('Calculations'[Date].[Date]),
'Calculations'[Date].[Date] <= MAX('Calculations'[Date].[Date])
)
)

 

The date formulas are in a seperate table as I have a few different tables that the dashboard is pulling from. 

 

And yes the 1 and 2 are weeks in the snapshot.

 

Many Thanks,

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi redhol,

 

The cause could be the [Date] in the dates. Please try the formula below.

Rolling Stock =
CALCULATE (
    SUM ( HR_StockMovements[MovementQty] ),
    FILTER (
        ALL ( 'Calculations'[Date]),
        'Calculations'[Date] <= MAX ( 'Calculations'[Date] )
    )
)

Rolling Stock =
CALCULATE (
    SUM ( HR_StockMovements[MovementQty] ),
    FILTER (
        ALL ( 'Calculations'[Date].[Date] ),
        'Calculations'[Date].[Date] <= MAX ( 'Calculations'[Date].[Date] )
    )
)

Best Regards,

Dale

  • redhol's avatar
    redhol
    Frequent Visitor
    7 years ago

    That works perfect. 

     

    Thanks for your help.