# Power pivot formula - find max price with conditions

#### DallDenmark

##### New Member
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.

 Project No_ Task Code Work Type Code Code Start Date Price 032839-003 SH E2 01-01-2015 720 040238-001 SH E2 01-01-2015 720 032839-003 SH E2 01-01-2016 725 040238-001 SH E2 01-01-2016 725 032839-003 30.17.1000 SH E2 01-01-2016 725 040238-001 30.17.1000 SH E2 01-01-2016 725 032839-003 10.07.1000 SH E2 01-01-2016 825 040238-001 10.07.1000 SH E2 01-01-2016 825 032839-003 10.07.1000 SH E2 01-01-2015 815 040238-001 10.07.1000 SH E2 01-01-2015 815 032839-003 30.17.1000 SH E2 01-01-2015 815 040238-001 30.17.1000 SH E2 01-01-2015 815

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### brawnystaff

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

#### DallDenmark

##### New Member
I'm using Excel 2016. Calculated column

#### macfuller

##### Active Member
Try this (with your own table name of course).
-- I used xCode for the last parameter so it wouldn't conflict with the HTML
Code:
`````` tag --

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

Last edited:

#### DallDenmark

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

#### DallDenmark

##### New Member
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?

Replies
4
Views
911
Replies
2
Views
265
Replies
1
Views
369
Replies
6
Views
854
Replies
8
Views
202

1,136,338
Messages
5,675,189
Members
419,553
Latest member
hanahass

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back