Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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)
Proud to be a PBI Community Champion
@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)
Proud to be a PBI Community Champion
@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?
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |