Help building a DAX formula

[shophoney]

Hi I'm looking at finding out what the last discount code is in a table.

table:"tblInvPriceDisc"
Fields:"start date","discount_amt"

So I would like to know the last date it went on sale and what the amount is. I would guess something with Max Value??

Then take the discount and reduce the "retail" in "tblinvskumaster". Does the table have to be related? It's connected to the calendar table for dates. So it gives me an error when I try to use the related function? Do I need to import a second table as I can only relate one field at a time?

One last thing. Sample problem:
=if('tblinvpriceDisc'[disc_type]="%",'tblinvskumaster[retail]*'tblinvpriceDisc'[disc_amt],if('tblinvpriceDisc'[disc_type]="$",'tblinvskumaster[retail]-'tblinvpriceDisc'[disc_amt],if('tblinvpriceDisc'[disc_type]="o",'tblinvpriceDisc'[disc_amt],)))

If i had the discount I would think something like above might work to figure out the current net price.

Thanks

 

[gazpage]

I think you'all get better answer if you can describe what the actual calculation is. 'the last date it was on sale' doesn't mean anything to me.

Do you mean "find the latest date in the start date column and find the value in the discount amount column that matches?' If so you want something like.

CALCULATE (
    VALUES ( table[discount amount] ),
    FILTER (
        ALL ( table[start date] ),
        table[start date] = MAX ( table[start date] )
    )
)