Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Im trying to write a code that can identify where the End Date is within 2 days of the next No's Start Date and to flag that for example
No | Start Date | End Date | |
100 | 23/03/2021 | 24/03/2021 | |
200 | 25/03/2021 | 30/03/2021 | |
100 | 26/03/2021 | 28/03/2021 | |
300 | 1/04/2021 | 3/04/2021 | |
300 | 10/04/2021 | 11/04/2021 | |
No | Start Date | End Date | Within 2 days for same No. |
100 | 23/03/2021 | 24/03/2021 | Yes |
200 | 25/03/2021 | 30/03/2021 | No |
100 | 26/03/2021 | 28/03/2021 | Yes |
300 | 1/04/2021 | 3/04/2021 | No |
300 | 10/04/2021 | 11/04/2021 | No |
Solved! Go to Solution.
Hi @MdJ83 ,
Please try the following formula:
Measure =
VAR lastenddate =
MAXX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[No] = MAX ( 'Table'[No] )
&& 'Table'[End Date] < MAX ( 'Table'[End Date] )
),
'Table'[End Date]
)
VAR nextstartdate =
MINX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[No] = MAX ( 'Table'[No] )
&& 'Table'[Start Date] > MAX ( 'Table'[Start Date] )
),
'Table'[Start Date]
)
VAR E_N_S = DATEDIFF ( MAX ( 'Table'[End Date] ), nextstartdate, DAY )
VAR S_L_E = DATEDIFF ( lastenddate, MAX ( 'Table'[Start Date] ), DAY )
RETURN
IF ( E_N_S <= 2 && S_L_E <= 2 && E_N_S + S_L_E <> BLANK (), "Yes", "No" )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, you can find the days between End and next Start date with this code:
Within 2 days for same No. =
Var _END = 'Table'[End Date]
Var _NextStart = CALCULATE(min('Table'[Start Date]),filter(all('Table'),'Table'[Start Date]>EARLIER('Table'[Start Date])&&'Table'[No]=EARLIER('Table'[No])))
Var _Diff = DATEDIFF(_END,_NextStart,DAY)
return
_Diff
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
HI @MdJ83
How did you calculate YES for second line of 100 with 28-Mar-21 end and no other line in the table?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
So the yes is because of line 1 and line 3 is taking leave within two days, by comparing the end date of each number (ID 100) and comparing it to the next occurance for that number (ID100) if the start date is within 2 days. Hope that makes sense.
Hi @MdJ83 ,
Please try the following formula:
Measure =
VAR lastenddate =
MAXX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[No] = MAX ( 'Table'[No] )
&& 'Table'[End Date] < MAX ( 'Table'[End Date] )
),
'Table'[End Date]
)
VAR nextstartdate =
MINX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[No] = MAX ( 'Table'[No] )
&& 'Table'[Start Date] > MAX ( 'Table'[Start Date] )
),
'Table'[Start Date]
)
VAR E_N_S = DATEDIFF ( MAX ( 'Table'[End Date] ), nextstartdate, DAY )
VAR S_L_E = DATEDIFF ( lastenddate, MAX ( 'Table'[Start Date] ), DAY )
RETURN
IF ( E_N_S <= 2 && S_L_E <= 2 && E_N_S + S_L_E <> BLANK (), "Yes", "No" )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
45 | |
18 | |
16 |