Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
Thank you so much, it was really helpful!
User | Count |
---|---|
57 | |
21 | |
19 | |
16 | |
16 |
User | Count |
---|---|
86 | |
77 | |
52 | |
37 | |
22 |