Help with Calculating Number of Events in a Nested Service Type Table in Power BI

[Nazdac2024]

Hi all,

I’m trying to solve a problem in Power BI with the following dataset structure:

• The table contains columns: Service Type, Start Date, and End Date.
• Each service type can have multiple related events (rows).
  
  Goal:

I need to calculate the number of events for each unique service type entry, based on the following logic:

1. For each service type row, count all subsequent rows (events) where:
   • The Service Type is blank (nested events).
   • The Start Date is less than or equal to the End Date of the original service type.
2. If there are identical nested service types within the range, adjust the end date dynamically to include those nested ranges.

Example:

Table Input:

• 

Steps:

• For Serv 1 (No 1):
  • The end date is 1/6/2025. Counting all rows where the start date is >= 1/1/2025, I get 5 events.
• For Serv 2 (No 7):
  • The initial end date is 1/10/2025. However, a nested Serv 2 (Row 9) ends on 1/12/2025, extending the range. Counting events in this range gives 5 events.
• For Serv 1 (No 26):
  • This is calculated separately, with an end date of 1/22/2025, resulting in 3 events.
    
    
    Does anyone know how I can achieve this?
    I have full control over the data model and can create calculated columns or measures if needed. Any guidance would be appreciated!

    Thanks in advance!

[ryan_mayu · Accepted solution]

@Nazdac2024 

For Serv 1 (No 1):

• The end date is 1/6/2025. Counting all rows where the start date is ≤ 1/6/2025,

should that be start date < 1/6/2025? it's because if <= , then we will get 6 rows

pls see the attachment below

[Nazdac2024]

Great , 
the start date for Serv 1 (No 1) is >= 1/1/2025
sorry for the mistake 

I ll will test this on my data set 🙂