Forum Discussion
Creating Dummy Date Field?
I have this table:
It shows over time when someone owned an account. I was provided an entry date only, so I had to come up with a date field to represent their exit date which is simply the date it was given to the next person:
So Latricia is currently the account owner meaning she does not yet have an exit date. How can I modify my formula to give her a dummy date far in the future (ex: 12/31/2100) until she has an official exit date loaded into the system? For another formula I'm working on, it will require that everyone has an exit date.
Anonymous Please try this...
ExitDate = VAR _ExitDate = CALCULATE(MIN(Test66Lkp[EntryDate]),FILTER(ALL(Test66Lkp),Test66Lkp[Code]=EARLIER(Test66Lkp[Code]) && Test66Lkp[Name] <> EARLIER(Test66Lkp[Name]) && Test66Lkp[EntryDate] > EARLIER(Test66Lkp[EntryDate]))) RETURN IF(_ExitDate=BLANK(),DATE(2100,12,31),_ExitDate)
3 Replies
Anonymous Please try this...
ExitDate = VAR _ExitDate = CALCULATE(MIN(Test66Lkp[EntryDate]),FILTER(ALL(Test66Lkp),Test66Lkp[Code]=EARLIER(Test66Lkp[Code]) && Test66Lkp[Name] <> EARLIER(Test66Lkp[Name]) && Test66Lkp[EntryDate] > EARLIER(Test66Lkp[EntryDate]))) RETURN IF(_ExitDate=BLANK(),DATE(2100,12,31),_ExitDate)
PattemManohar Thanks! One question I do have. So let's say Latricia no longer becomes the owner and there is another person after her. Would Latricia's eixt date change to whatever date that was, and would the new person then be assigned 12/31/2100?
- Anonymous Yes it will do. Please change the data by adding another owner and test the same. It should work dynamically.