Finding the most recent value change

[legendsfan]

Requirement:  Create a custom column that lists the most recent updated start date.  
Background: PSOW # is always required. PCR # is only used if there are ammendments to orginal PSOW #.  PCR # always starts with 1 and counts up.  Est Start Date is not required to be updated in a PCR.  There can be multiple PCR #'s and for capturing date I'm only concerned with the most recent change to start date.

Problem: I need a way to scan backwards from most recent change to original PSOW # which has no PCR #.

Explanation of PSOW # 100:  There are 4 entries (original submission and 3 PCR.  I don't want date from most recent PCR #3 because it is NULL.  I need the most recent PCR entry where there was a date.

How can I capture in a custom column the most recent Est Start Date entry for each PSOW?

Source Data:

PSOW #	PCR #	Cost	Est Start Date
100		10000	1/1/2017
100	1	2000	2/1/2017
100	2	3000
100	3	2000
101		5000	3/1/2017
101	1	2000
101	2	1000

Expected Results:

PSOW #	Cost	Current Start Date
100	17000	2/1/2017
101	8000	3/1/2017

[dkay84_PowerBI · Accepted solution]

I wasn't following your description, but purely by looking at your expected result I suspect you can acheive this easily in the query editor.

You can "Group By" PSOW# and add two aggregations: SUM of Cost and MAX of Start Date.  See if that works.  I'm not 100% sure about the MAX of Start Date but try it and let us know

[parry2k]

MAX function works with dates, so in this it will be ok.

[legendsfan]

If I use Max or LastNonBlank how do I ensure my table is sorted properly where it always evaluates the highest number PCR # all the way down to the blank PCR #? For code example I'll use lastnonblank.  Basically I need to sort by PCR # before returning LastNonBlank.

Current Start Date =
LASTNONBLANK ( PSOW[Est Start date], 1 )

[dkay84_PowerBI]

Not sure I follow you. Did you follow my steps? That will produce the result you showed

[legendsfan]

I edited my previous post as it looks like I left out a couple words 🙂  I chose to use a Measure using LastNonBlank instead of Max but assume both will work.  What I'd like to do within the code of the measure is ensure the data is sorted correctly so it always evaluates this highest PCR # first.