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 as I do not have a "Stock on Date" field our ERP but I do have a ledger of all our stock movements (sales, delvieries, returns etc. displayed as +1 / -1 and so on.

 

What I want to be able to do is have a table split by week and the table display the position up to the close of that week, example in the picture.

The problem I have at the moment is that my formula is calculating that week only and not the value up to that date (or week end in this case).

 

Hope this makes sense and any help is appreciated!

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 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

4 Replies

  • 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
        8 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