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

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
Joined
Aug 9, 2012
Messages
95
Office Version
  1. 365
What version of Excel are you using? Are you looking for a measure or calculated column?
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
298
Office Version
  1. 365
Platform
  1. Windows
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:

DallDenmark

New Member
Joined
May 3, 2018
Messages
5
It does not work :( No errors, just blank. Can I send a data file - I cannot attach here?
 

DallDenmark

New Member
Joined
May 3, 2018
Messages
5
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?
 

Forum statistics

Threads
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.
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
Top