Power pivot formula - find max price with conditions

DallDenmark

New Member
Joined
May 3, 2018
Messages
5
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 CodeWork Type CodeCodeStart DatePrice
032839-003SHE201-01-2015720
040238-001SHE201-01-2015720
032839-003SHE201-01-2016725
040238-001SHE201-01-2016725
032839-00330.17.1000SHE201-01-2016725
040238-00130.17.1000SHE201-01-2016725
032839-00310.07.1000SHE201-01-2016825
040238-00110.07.1000SHE201-01-2016825
032839-00310.07.1000SHE201-01-2015815
040238-00110.07.1000SHE201-01-2015815
032839-00330.17.1000SHE201-01-2015815
040238-00130.17.1000SHE201-01-2015815

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

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
What version of Excel are you using? Are you looking for a measure or calculated column?
 
Upvote 0
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[Task Code] = EARLIER ( tablename[Task Code] )
            && tablename[xCode] = EARLIER ( tablename[xCode] )
    )
)
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top