Getting 1 or 0 at a row with a new contract

[Anonymous]

soo I got this problem I cant solve myself.

I got this dataset that contains the following columns
employee_name | fte | employement | employed_since | out_service_since

this dataset is filled with rows with multiple contracts like this, you should remember that this dataset is filled with multiple employees and it is not always sorted on earliest day, this is because it is like log and it is sorted by day of log, so basically every day they will keep tracks of every employee:
example of log(if there is for example a harry and a william:
day of log | employee_name | fte | employement | employed_since | out_service_since
10th of january | harry styles | 1 | regular staff | 10th of January 2022 | 6th of August 2022
10th of january | william shakespeare | 1 | regular staff | 8th of January 2022 | 7th of October 2022
11th of january | harry styles | 1 | regular staff | 10th of January 2022 | 6th of August 2022
11th of january | william shakespeare | 1 | regular staff | 8th of January 2022 | 7th of October 2022
12th of january | harry styles | 1 | regular staff | 10th of January 2022 | 6th of August 2022
12th of january | william shakespeare | 1 | regular staff | 8th of January 2022 | 7th of October 2022


example of one employee sorted by earliest employed_since without day of log:
employee_name | fte | employement | employed_since | out_service_since
harry styles | 1 | regular staff | 10th of January 2022 | 6th of August 2022
harry styles | 1 | regular staff | 7th of August2022 | 8th of September 2022
harry styles | 0 | on-call worker | 9th of September 2022 | 15th of December 2022
harry styles | 0 | on-call worker | 16th of December 2022 | 8th of March 2023
harry styles | 0 | on-call worker | 9th of March 2023 | NULL

I want a 1 or 0 zero based on multiple conditions:
-only get a 1 if fte is 0
-only a 1 with the earliest employed_since, except if employed_since differs more than 3 days with the last out_of_service

the result be like this 
harry styles | 1 |  regular staff | 10th of January 2022 | 6th of August 2022 | 0 
harry styles | 1 | regular staff | 7th of August2022 | 8th of September 2022 | 0
harry styles | 0 | on-call worker | 9th of September 2022 | 15th of December 2022 | 1
harry styles | 0 | on-call worker | 16th of December 2022 | 8th of March 2023 | 0
harry styles | 0 | on-call worker | 9th of March 2023 | 15th of May 2023 | 0
harry styles | 0 | on-call worker | 20th of May 2023 | 15th of July 2023 | 1

Notice that the last one is also a 1 cuz the employed_since and out_of_service differs more than 3 days

Can someone help me with this??

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the proposed solution

Result = 
IF ( 
    'Table'[fte ] = 0,
    VAR CurrentEmpSince = 'Table'[employed_since ]
    VAR CurrentOutSince = 'Table'[out_service_since]
    VAR CurrentEmpTable = 
        FILTER ( 
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[employee_name] ) ),
            'Table'[fte ] = 0
        )
    VAR TableBefore = FILTER ( CurrentEmpTable, 'Table'[employed_since ] < CurrentEmpSince )
    VAR PreviousOutSince = MAXX ( TableBefore, 'Table'[out_service_since] )
    RETURN
        IF ( 
            INT ( CurrentEmpSince - PreviousOutSince ) > 3,
            1,
            0
        ),
    0
)

[Anonymous]

This one worked for me, thank you very much!!

[FreemanZ]

could you post some usable data?

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...