Calculation of the days between the beginning and the end of a series of steps

[Luzadriana255]

Hello Everyone

I have a process with different requests (A, B) and 8 steps. The steps have a date when they were completed, but some dates are missing and a default date "01/01/2000" was used to fill these gaps. I need to measure how many days it takes to complete steps 3 through 7 (which is possible if the date is not 01/01/2000). If the date is 01/01/2000, I have to compare with the first and the last step with other available dates between 3 and 7. I need to add this number of days in a new column (Days Comparison) for all steps of this request.

For example, for request A I compared the period 02/04/2022 to 01/07/2022 = 90 days.

And for request B I compared the period from 04/03/2022 to 22/06/2022 = 110 days.

I appreciate your help on how to create the Days comparison column. Thank you!

Request	Step	Date	Days comparison
A	1	12.03.2022	90
A	2	22.03.2022	90
A	3	02.04.2022	90
A	4	28.04.2022	90
A	5	29.04.2022	90
A	6	12.06.2022	90
A	7	01.07.2022	90
A	8	30.08.2022	90
B	1	14.02.2022	110
B	2	01.01.2000	110
B	3	01.01.2000	110
B	4	04.03.2022	110
B	5	22.05.2022	110
B	6	22.06.2022	110
B	7	01.01.2000	110
B	8	01.01.2000	110

[tamerj1 · Accepted solution]

@Luzadriana255 
Please refer to attached sample file with the proposed solution

Days comparison = 
VAR CurrentRequestTable = 
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Request] ) )
VAR FilteredTable = 
    FILTER ( 
        CurrentRequestTable, 
        'Table'[Date] <> DATE ( 2000, 1, 1 )
            && 'Table'[Step] >= 3
            && 'Table'[Step] <= 7
    )
VAR FirstRecord = 
    TOPN ( 1, FilteredTable, 'Table'[Step], ASC )
VAR LastRecord = 
    TOPN ( 1, FilteredTable, 'Table'[Step] )
VAR StartDate = MAXX ( FirstRecord, 'Table'[Date] )
VAR EndDate = MAXX ( LastRecord, 'Table'[Date] )
VAR Result =
    DATEDIFF ( StartDate, EndDate, DAY )
RETURN
    Result

[Luzadriana255]

Thank you so much, it was really helpful!