Count based on same end and start date

[RichOB]

Hi,

When the next Start_Date is the same as the previous End_Date (or before) for the same "Name", I need this to count as 1. 

For example:

Glasgow had an end date of 01/02/2025 for r111; the next start date for Glasgow is 01/02/2025 for r222, as this is the same day I need this to count as 1.

Derby had an expiry date in FY 24/25, but the next Start_Date was 3 days before, which is also counted as 1.

Name	Ref	Start_Date	End_Date
Glasgow	r111	01/02/2024	01/02/2025	*due fy24/25
Glasgow	r222	01/02/2025	05/02/2026
Edinburgh	r333	25/03/2024	25/03/2025	*due fy24/25
Edinburgh	r444	27/03/2025	27/03/2025
Liverpool	r555	02/01/2024	02/01/2025	*due fy24/25
Liverpool	r666	02/01/2025	02/01/2026
Manchester	r777	06/08/2024	06/08/2025
Manchester	r888
Derby	r999	07/07/2023	07/07/2024	*due fy24/25
Derby	r000	04/07/2024	09/07/2025

For the financial year 24/25 (April24 to March25)

I make this as 3 were on time:

Glasgow. Completed on time = 1

Edinburgh. Completed late = 0

Liverpool. Completed on time = 1

Manchester. The current end date is due FY 25/26, not FY 24/25, and needs to be a 0

Derby. The newest start date is 3 days before the previous end date. This is classed as Completed on time = 1

Thanks for your help

[v-pgoloju]

Hi @RichOB,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @RichOB,

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @RichOB,

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju · Accepted solution]

Hi @RichOB,

Thank you for reaching out to the Microsoft Fabric Forum Community. And thnaks to @FBergamaschi for promt and helpful response.

I’ve tried to reproduce the scenario using the M code below. Please review and adjust it according to your data source. If the issue still persists, feel free to share more details, and we’ll be happy to assist further.

Thanks & Regards,

Prasanna Kumar

[FBergamaschi]

I am a bit confused, this is the table you provided

Name	Ref	Start_Date	End_Date
Glasgow	r111	01/02/2024	01/02/2025	*due fy24/25
Glasgow	r222	01/02/2025	05/02/2026
Edinburgh	r333	25/03/2024	25/03/2025	*due fy24/25
Edinburgh	r444	27/03/2025	27/03/2025
Liverpool	r555	02/01/2024	02/01/2025	*due fy24/25
Liverpool	r666	02/01/2025	02/01/2026
Manchester	r777	06/08/2024	06/08/2025
Manchester	r888
Derby	r999	07/07/2023	07/07/2024	*due fy24/25
Derby	r000	04/07/2024	09/07/2025

you write

When the next Start_Date is the same as the previous End_Date (or before) for the same "Name", I need this to count as 1. 

For example:

Glasgow had an end date of 01/02/2025; the next start date for A1 was later than 01/02/2025. I need this to count as 1.

But I see in Glasgow row with Ref r111 end date 01/02/2025 and next row r222 start date identical, not later than 01/02/25 as you write, what am I missing?

Derby had an expiry date in FY 24/25, but the next Start_Date was 3 days before, which is also counted as 1.

So wat should be ocunted as zero? Please can you recap what you want to see and where?

[RichOB]

My apologies I thought I had changed that. It should write 

Glasgow had an end date of 01/02/2025; the next start date for Glasgow is 01/02/2025, as this is the same day I need this to count as 1.

A new ref can have a start date that is before the previous end date, which is why Derby is a 1.

A 0 count would be if the new start date is later than the previous end date. For example, Edinburgh r333 end date is 25/03/2025, and the next start date for Edinburgh is 27/3/2025. It's clased as being late.

Just to recap

If the new start date is the same as the previous expiry for the same "Name = 1 

If the new start date is before the previous expiry for the same "Name" = 1 

If the new start date is later than the previous expiry for the same "Name" = 0

Thanks again