Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
einrikr
Frequent Visitor

Determine contract end date based on value from different row

Hi all,

 

I have a data set like below: 

Reporting DateEmployee numberNamePoolStart dateEnd date
1-1-20211Peter Sales01-01-2003 
1-2-20211PeterSales01-01-2003 
1-3-20211Peter Quality management15-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? 

2 ACCEPTED SOLUTIONS
v-easonf-msft
Community Support
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 )

17.png

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.

View solution in original post

Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
einrikr
Frequent Visitor

 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!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-easonf-msft
Community Support
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 )

17.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.