Power pivot formula - find max price with conditions

[DallDenmark]

I have a data set where below is an example.

I need to find the price with the latest start data, filtered by project no, task code and code.

So for Project no 032839-003 for code "E2" and task code "blank", the correct price is 725 since the latest start data is 01-01-2016.

[TABLE="width: 628"]
<tbody>[TR]
[TD]Project No_[/TD]
[TD]Task Code[/TD]
[TD]Work Type Code[/TD]
[TD]Code[/TD]
[TD]Start Date[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]032839-003[/TD]
[TD][/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2015[/TD]
[TD]720[/TD]
[/TR]
[TR]
[TD]040238-001[/TD]
[TD][/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2015[/TD]
[TD]720[/TD]
[/TR]
[TR]
[TD]032839-003[/TD]
[TD][/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2016[/TD]
[TD]725[/TD]
[/TR]
[TR]
[TD]040238-001[/TD]
[TD][/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2016[/TD]
[TD]725[/TD]
[/TR]
[TR]
[TD]032839-003[/TD]
[TD]30.17.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2016[/TD]
[TD]725[/TD]
[/TR]
[TR]
[TD]040238-001[/TD]
[TD]30.17.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2016[/TD]
[TD]725[/TD]
[/TR]
[TR]
[TD]032839-003[/TD]
[TD]10.07.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2016[/TD]
[TD]825[/TD]
[/TR]
[TR]
[TD]040238-001[/TD]
[TD]10.07.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2016[/TD]
[TD]825[/TD]
[/TR]
[TR]
[TD]032839-003[/TD]
[TD]10.07.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2015[/TD]
[TD]815[/TD]
[/TR]
[TR]
[TD]040238-001[/TD]
[TD]10.07.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2015[/TD]
[TD]815[/TD]
[/TR]
[TR]
[TD]032839-003[/TD]
[TD]30.17.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2015[/TD]
[TD]815[/TD]
[/TR]
[TR]
[TD]040238-001[/TD]
[TD]30.17.1000[/TD]
[TD]SH[/TD]
[TD]E2[/TD]
[TD]01-01-2015[/TD]
[TD]815[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

How do I make a formula in Powerpivot, giving me this result?

 

[brawnystaff]

What version of Excel are you using? Are you looking for a measure or calculated column?

 

[DallDenmark]

I'm using Excel 2016. Calculated column

 

[macfuller]

Try this (with your own table name of course).
-- I used xCode for the last parameter so it wouldn't conflict with the HTML

 tag --

[CODE]Latest Price =
CALCULATE (
    MAX ( Price ),
    FILTER (
        tablename,
        MAX ( tablename[Start Date] ) = tablename[Start Date]
            && tablename[Project No_] = EARLIER ( tablename[Project No_] )
            && tablename[Task Code] = EARLIER ( tablename[Task Code] )
            && tablename[xCode] = EARLIER ( tablename[xCode] )
    )
)

 

[DallDenmark]

It does not work No errors, just blank. Can I send a data file - I cannot attach here?

 

[DallDenmark]

The formula works when I copy the data to a table and uses this. Any idea why the formula does not work on data imported through Power query? Some kind of formatting?