Forum Discussion

RichOB's avatar
RichOB
Post Partisan
1 year ago
Solved

Count based on same end and start date

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; th...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
FBergamaschi
Super User
1 year ago

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

 

NameRefStart_DateEnd_Date 
Glasgowr11101/02/202401/02/2025*due fy24/25
Glasgowr22201/02/202505/02/2026 
Edinburghr33325/03/202425/03/2025*due fy24/25
Edinburghr44427/03/202527/03/2025 
Liverpoolr55502/01/202402/01/2025*due fy24/25
Liverpoolr66602/01/202502/01/2026 
Manchesterr77706/08/202406/08/2025 
Manchesterr888   
Derbyr99907/07/202307/07/2024*due fy24/25
Derbyr00004/07/202409/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's avatar
RichOB
Post Partisan
1 year ago

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