DAX find earliest date when lowest/higher cost happened

[Anonymous]

Hi All,

I'm trying to create a column that returns the earliest day when lowest and highest cost happened. I have a table with header "sku_id","cost","order_date", and I have tried the formula "Min[order_date] if [cost] is max" ; but the formula didn't ran through.

Do any of you DAX geniuses know where I'm going wrong? Any help is much appreciated!

Thanks in advance!

[danextian · Accepted solution]

hI @Anonymous
Assuming that this is on a per sku level, try these:

Earliest Date - Lowest Cost =
VAR __LOWEST_COST =
    CALCULATE ( MIN ( 'Table'[cost ] ), ALLEXCEPT ( 'Table', 'Table'[sku] ) )
RETURN
    CALCULATE (
        MIN ( 'Table'[order date] ),
        ALLEXCEPT ( 'Table', 'Table'[sku] ),
        'Table'[cost ] = __LOWEST_COST
    )

Eearliest Date - Highest Cost =
VAR __HIGHEST_COST =
    CALCULATE ( MAX( 'Table'[cost ] ), ALLEXCEPT ( 'Table', 'Table'[sku] ) )
RETURN
    CALCULATE (
        MIN( 'Table'[order date] ),
        ALLEXCEPT ( 'Table', 'Table'[sku] ),
        'Table'[cost ] = __HIGHEST_COST
    )

[Anonymous]

This works! thank you so much 🙂

[amitchandak]

@Anonymous , Try like these .

Date for max= calculate(lastnonblankvalue([cost], max([order_date])),filter(table,[sku_id] =earlier(sku_id)))
date for min = calculate(firstnonblankvalue([cost], max([order_date])),filter(table,[sku_id] =earlier(sku_id)))

if not

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.