Forum Discussion
Determine contract end date based on value from different row
Hi all,
I have a data set like below:
| Reporting Date | Employee number | Name | Pool | Start date | End date |
| 1-1-2021 | 1 | Peter | Sales | 01-01-2003 | |
| 1-2-2021 | 1 | Peter | Sales | 01-01-2003 | |
| 1-3-2021 | 1 | Peter | Quality management | 15-03-2021 |
Now, I have a file with quite a bunch of switchers, for whom I want to calculate the amount of contract hours per day.
I can do that by linking the above file to a file with contract hours per pool, and a calendar. Easy peasy. But because the data set above is a bit messy in the sense that end date registration is done quite poorly (i.e.: not done at all), I need to ajust end dates whenever somebody switched from pool.
In cases like this, I need the end date for row number 1 and 2 to be '14-03-2021' and ideally, I'd like Power BI to do that for me. As I have about a 150 switchers up until now, and quite a few of those cases per month, I'm not really looking to manually add this to the source file. I've been trying to come up with a super sweet solution here but I'm stuck in my thinking. I guess there should be a way to have Power BI determine a date (or rather: date - 1 day) based upon the data in another row.
My thinking was to create a piece of magic that is saying: look at the employee number and the pool name for this row, and see if both are the same for the next row. If not, then take the start date of the next row, detract 1 day from it and register that in the End Date column. That'd be nice but I honestly don't know how to do so, nor if this is the sweetest possible solution. Could anyone please help me with this?
Hi, einrikr
Try a calculated column like the following:
End Date = VAR maxdate = CALCULATE ( MAX ( 'Table'[Start date] ), FILTER ( 'Table', 'Table'[Employee number] = EARLIER ( 'Table'[Employee number] ) ) ) // Select the largest date after grouping by 'Employee number' RETURN IF ( 'Table'[Start date] = maxdate, BLANK (), maxdate - 1 )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
This calculated column formula works
=if(CALCULATE(countrows(Data),FILTER(Data,Data[Employee number]=EARLIER(Data[Employee number])&&Data[Start date]>EARLIER(Data[Start date])))=0,BLANK(),CALCULATE(MIN(Data[Start date])-1,FILTER(Data,Data[Employee number]=EARLIER(Data[Employee number])&&Data[Start date]>EARLIER(Data[Start date]))))Hope this helps.
4 Replies
- v-easonf-msft
Community Support
Hi, einrikr
Try a calculated column like the following:
End Date = VAR maxdate = CALCULATE ( MAX ( 'Table'[Start date] ), FILTER ( 'Table', 'Table'[Employee number] = EARLIER ( 'Table'[Employee number] ) ) ) // Select the largest date after grouping by 'Employee number' RETURN IF ( 'Table'[Start date] = maxdate, BLANK (), maxdate - 1 )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. - Ashish_Mathur
Super User
Hi,
This calculated column formula works
=if(CALCULATE(countrows(Data),FILTER(Data,Data[Employee number]=EARLIER(Data[Employee number])&&Data[Start date]>EARLIER(Data[Start date])))=0,BLANK(),CALCULATE(MIN(Data[Start date])-1,FILTER(Data,Data[Employee number]=EARLIER(Data[Employee number])&&Data[Start date]>EARLIER(Data[Start date]))))Hope this helps.
Hi guys, thanks a ton for your solutions, I found they both work so I've selected both as a possible solution. It helped me out fantastically so I'm very grateful. Thanks very much for your help, much appreciated!
- Ashish_Mathur
You are welcome.
