Forum Discussion
Finding the most recent value change
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 |
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
5 Replies
- dkay84_PowerBI
Microsoft Employee
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
Super User
MAX function works with dates, so in this it will be ok.
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 )
