Compare the cost of a Unique Part Number last entry again second last entry

[soldanr]

Hello Power Bier's,

I am half way into the saga of finding a value for the difference in cost of a unique part number on my direct query table.

The value are:

The goal is to calculate the difference of the last entry by date minus the second last entry by date for a unique part number.

As an Example:

Part Number A had a cost of $11 dollars on 6/5/2018 and the last time we made them it was 4/4/2018 at a cost of $14.

So Idealy the first row shows a column with Cost Diff = $-3 (part was cheaper to make).

That would repeat for all rows comparing the current date with previous date.

The equation to find the second last date (prior date) is the following:

Prior Date = CALCULATE(MAX(Finish[Date]), FILTER(Finish, Finish[Date] < MAX(Finish[Date])))

But I am stuck bulding a table like the one above

LINK for the file is here

[LivioLanzo]

Are you looking to build a calculated column?

This DAX may help you:

Column =
VAR PrevDate =
    CALCULATE (
        MAX ( Finish[Date] ),
        ALLEXCEPT ( Finish, Finish[PartNumber] ),
        Finish[Date] < EARLIER ( Finish[Date] )
    )
VAR PrevCost =
    CALCULATE (
        VALUES ( Finish[Cost] ),
        ALLEXCEPT ( Finish, Finish[PartNumber] ),
        Finish[Date] = PrevDate
    )
RETURN
    IF ( ISBLANK ( PrevCost ), BLANK (), Finish[Cost] - PrevCost )

[soldanr]

Thank you very much. This solution worked for the example.

I think a calculated column is what I need. However, I could only use a new measure column on my actual table.

It did not work for the actual table I am working on.

Dax Error was: "EARLIER/EARLIEST refers to an earlier row context wich doesn't exist"

[soldanr]

Looks like I won't be able to make a calculated column on a direct query.

Unfortunately the measured column is the only one that will work. 

[LivioLanzo · Accepted solution]

try this one

Measure =
VAR MxDt =
    CALCULATE (
        MAX ( Data[Date] ),
        ALLEXCEPT ( Data, Data[PartNmber] ),
        Data[Date] < CALCULATETABLE ( VALUES ( Data[Date] ) )
    )
RETURN
    VAR PrevCost =
        CALCULATE (
            VALUES ( Data[Cost] ),
            ALLEXCEPT ( Data, Data[PartNmber] ),
            Data[Date] = MxDt
        )
    RETURN
        IF (
            ISBLANK ( PrevCost ),
            BLANK (),
            CALCULATETABLE ( VALUES ( Data[Cost] ) ) - PrevCost
        )

[soldanr]

That worked out really well!

The measure column makes the calculation very slow, but that is okay.

Thanks for the help!