Forum Discussion
Last Date Modified (Column vs Column)
Hello,
I have a data set with items and their contract price. These contracts are modified by either the branch or the corporate headquarters. These are each in different columns. I would like to pull the last time the item was modified, regardless of where it was modified. ie, given the sample data, I want to create that last column in BI where it picks the last date between the two.
| Item | Last Modified - Corporate | Last Modified - Branch | Last Modified |
| A | 10/1/2014 | 10/1/2014 | |
| B | 12/3/2013 | 12/3/2013 | |
| C | 1/15/2015 | 3/8/2014 | 1/15/2015 |
| D | 3/4/2012 | 4/9/2013 | 4/9/2013 |
| E | 5/10/2015 | 6/10/2014 | 5/10/2015 |
| F | 5/10/2013 | 5/10/2013 | |
| G | 10/5/2015 | 10/5/2015 |
Thanks for any help!
You should be able to do this with a nested IF statement to see which column has the older date. The first two IF statements check to see if the column is blank. If they are, it returns the other column. The final IF statement compares the two columns if they're not blank and gives you the older one.
lastMod = IF(ISBLANK([Last Modified - Branch]),[Last Modified - Corporate], IF(ISBLANK(Table1[Last Modified - Corporate]),[Last Modified - Branch], IF([Last Modified - Branch]<=[Last Modified - Corporate],[Last Modified - Branch], [Last Modified - Corporate])))
2 Replies
You should be able to do this with a nested IF statement to see which column has the older date. The first two IF statements check to see if the column is blank. If they are, it returns the other column. The final IF statement compares the two columns if they're not blank and gives you the older one.
lastMod = IF(ISBLANK([Last Modified - Branch]),[Last Modified - Corporate], IF(ISBLANK(Table1[Last Modified - Corporate]),[Last Modified - Branch], IF([Last Modified - Branch]<=[Last Modified - Corporate],[Last Modified - Branch], [Last Modified - Corporate])))
th3h0bb5
I had tried that and it didn't work... Turns out one of my date categories was formatted as a text. :smileymad:Thanks for your help