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

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

Hi redhol,

 

Can you share a sample?

What's your formula for now? 

Are the 1 and 2 in the snapshot weeks?

 

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.