Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lund
Frequent Visitor

Calculate cumulative stock value

Hi

 

I have this messaure where i do a cumulativestock quantity. Works like a charm!

_cuml Stock QTY = 

CALCULATE (

    SUM ( 'Item ledger Entry'[Quantity] ),

    FILTER ( ALL ( 'Item ledger Entry' ), 'Item ledger Entry'[Posting Date] <= MAX ( 'Item ledger Entry'[Posting Date] ) )

)
 
But i also need a messaure the stock value. 
I have a related table Products where i got the [Internal Cost].
bi.png
Then i need somehow to multiply my sum of quantity with [Internal Cost]
 
 
_Cuml Stock Value = 

CALCULATE (

    SUMX ( Products, Products[Internal Cost] * RELATED('Item ledger Entry'[Quantity]) ),

    FILTER ( ALL ( 'Item ledger Entry'), 'Item ledger Entry'[Posting Date] <= MAX ('Item ledger Entry'[Posting Date]) )

)
 But RELATED() dosent work. Get this error: The parameter is not of the correct type
 
Can you help me with the solution?
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Lund , related can be used for table on one side

 

_Cuml Stock Value =

CALCULATE (

SUMX ('Item ledger Entry' , realted(Products[Internal Cost]) * ('Item ledger Entry'[Quantity]) ),

FILTER ( ALL ( 'Item ledger Entry'), 'Item ledger Entry'[Posting Date] <= MAX ('Item ledger Entry'[Posting Date]) )

)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Lund , related can be used for table on one side

 

_Cuml Stock Value =

CALCULATE (

SUMX ('Item ledger Entry' , realted(Products[Internal Cost]) * ('Item ledger Entry'[Quantity]) ),

FILTER ( ALL ( 'Item ledger Entry'), 'Item ledger Entry'[Posting Date] <= MAX ('Item ledger Entry'[Posting Date]) )

)

Oh, okay. Thanks alot! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.