Total cost per validity date

Hi Anonymous,

 

Try this measure. You can play around with different dates by setting the vToday variable.

 

Total Cost = 
VAR vToday =
    TODAY()
    --DATE(2021, 3, 31)
VAR vCurrentItem =
    MAX ( Items[ITEM_NO] )
VAR vRowInDateRange =
    FILTER ( Items, vToday >= Items[FROM_DATE] && vToday <= Items[TO_DATE] )
VAR vResultInDateRange =
    SUMX ( vRowInDateRange, Items[Main Price] * Items[Total QTY] )
VAR vItemMaxDate =
    CALCULATE ( MAX ( Items[TO_DATE] ) )
VAR vRowWithMaxDate =
    FILTER ( Items, Items[TO_DATE] = vItemMaxDate )
VAR vResultWithMaxDate =
    SUMX ( vRowWithMaxDate, Items[Main Price] * Items[Total QTY] )
VAR vResult =
    IF ( ISBLANK ( vResultInDateRange ), vResultWithMaxDate, vResultInDateRange )
RETURN
    vResult

Anonymous,

 

Try this measure. There are a few differences compared to your example result, like SUPPLIER 16230 / ITEM_NO 274028 (your example shows FALSE for each row, but shouldn't it use the latest price since today isn't between any FROM_DATE / TO_DATE?).

 

Total Cost = 
VAR vToday =
    --TODAY()
    DATE ( 2021, 2, 18 )
VAR vMainPrice =
    MAX ( Items[Main Price] )
VAR vTotalQty =
    SUM ( Items[Total QTY] )
VAR vLatestToDate =
    CALCULATE (
        MAX ( Items[TO_DATE] ),
        ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] )
    )
VAR vLatestPrice =
    CALCULATE (
        MAX ( Items[Main Price] ),
        ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] ),
        Items[TO_DATE] = vLatestToDate
    )
VAR vNumRowsTodayInRange =
    CALCULATE (
        COUNTROWS ( Items ),
        ALLEXCEPT ( Items, Items[SUPPLIER], Items[ITEM_NO] ),
        vToday >= Items[FROM_DATE]
            && vToday <= Items[TO_DATE]
    )
VAR vResult =
    SWITCH (
        TRUE (),
        --if vToday is between FROM_DATE / TO_DATE, use current price
        vToday >= MAX ( Items[FROM_DATE] )
            && vToday <= MAX ( Items[TO_DATE] ), vMainPrice * vTotalQty,
        --if vToday is not between any FROM_DATE / TO_DATE, use latest price
        vNumRowsTodayInRange = 0
            && MAX ( Items[TO_DATE] ) = vLatestToDate, vLatestPrice * vTotalQty
    )
RETURN
    vResult