Forum Discussion

russd's avatar
russd
New Member
3 years ago
Solved

Future aging inventory balances

I have two table. One is a Date table and the other is a STOCK table. The STOCK table contains lines of inventory and has three columns, Part Number, Receive Date, and Value. I joined the Receive Dat...
  • Martin_D's avatar
    Martin_D
    3 years ago

    Hi russd,

    this measure should make your day:

    Aging Stock Amt. > 3m = 
VAR _ReceiveDatesUpTo = 
    CALCULATE (
        MAX ( 'Date'[Date] ),
        DATEADD ( 'Date'[Date], -3, month )
    )
RETURN
    CALCULATE (
        SUM ( 'STOCK'[Value] ),
        'Date'[Date] <= _ReceiveDatesUpTo,
	    ALL ( 'Date' )
    )

    The first CALCULATE calculates the last receive date to include.
    The second CALCULATE calculates the total received stock value for all past dates up to the last receive date to include.

    Download example here

Martin_D's avatar
Martin_D
Icon for Solution Sage rankSolution Sage
3 years ago

Hi russd,

this measure should make your day:

Aging Stock Amt. > 3m = 
VAR _ReceiveDatesUpTo = 
    CALCULATE (
        MAX ( 'Date'[Date] ),
        DATEADD ( 'Date'[Date], -3, month )
    )
RETURN
    CALCULATE (
        SUM ( 'STOCK'[Value] ),
        'Date'[Date] <= _ReceiveDatesUpTo,
	    ALL ( 'Date' )
    )

The first CALCULATE calculates the last receive date to include.
The second CALCULATE calculates the total received stock value for all past dates up to the last receive date to include.

Download example here