Forum Discussion

JR_Lee's avatar
JR_Lee
Frequent Visitor
4 years ago
Solved

Calculating cumulative stock value

StockValue = CALCULATE( SUM ('Ledger Entry'[Quantity] * 'Ledger Entry'[Current Value] ) FILTER (ALL(Date), Date[Date] <= MAX('Date'[Date]))) Hi,   I have a data s...
  • smpa01's avatar
    smpa01
    4 years ago

    JR_Lee  and if you don't want to create dimension tables

     

    cumulativeQuantity =
CALCULATE (
    SUM ( t2[Quantity] ),
    FILTER ( ALL ( t2 ), t2[Posting Date] <= MAX ( t2[Posting Date] ) )
)


_Cumulative Stock Value = 
CALCULATE (
    SUMX ( t1, t1[Standard Cost] * related(t2[Quantity]) ),
    FILTER ( ALL ( t2), t2[Posting Date] <= MAX ( t2[Posting Date] ) )
)

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

JR_Lee  what is the expected output based on the sample data that you provided?

JR_Lee's avatar
JR_Lee
Frequent Visitor
4 years ago

Hi smpa01 
Not too sure exactly as I just typed up the data in my reply as a loose example and my table has 8m rows.
The expected output should be the cumulative result of standard cost x current value of standard cost - I think?
Sorry if this is not clear.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    JR_Lee  can you take a moment to take a look into what you provided and what it should yield to?

    • JR_Lee's avatar
      JR_Lee
      Frequent Visitor
      4 years ago
      DateCumulative QtyCumulative Stock Value
      02/10/2021-46-£1,220.85
      03/10/2021441£12,191.13
      04/10/202122£12,156.67

       

      Hi, so this should be the result of the data I provided above. Does this help?

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        JR_Lee  and how did you reach to this number? Can you describe the logic?

         

        I only tested Cumulative Qty and it does not make sense to me how you reached at -46 for 2021-10-02 while sum of all the quantity for that amounts to -21?

         

        Please clearly describe how did you reach to each of those numbers for each of the day for both Cumulative Qty and Cumulative Stock Value