Get rolling sum of customers based on start and end dates

[Traceout]

I'm looking to get a rolling count of customers based on the start and end date so I can see where the peak of customers affected is. Any help with the formula would be greatly appreciated!

Table Data: 

ID	Start_Date	End_date	Customers
1	12/11/22 4:58 AM	12/11/22 4:31 PM	97
2	12/11/22 5:43 AM	12/11/22 6:30 AM	12
3	12/11/22 6:19 AM	12/11/22 12:12 PM	71
4	12/11/22 6:31 AM	12/11/22 6:52 AM	20
5	12/11/22 6:43 AM	12/11/22 7:55 AM	3
6	12/11/22 7:07 AM	12/11/22 8:32 AM	1

Expected Data with new 'Rolling Customers impacted' column:

ID	Start_Date	End_date	Customers	Rolling Customers Impacted
1	12/11/22 4:58 AM	12/11/22 4:31 PM	97	97
2	12/11/22 5:43 AM	12/11/22 6:30 AM	12	109
3	12/11/22 6:19 AM	12/11/22 12:12 PM	71	180
4	12/11/22 6:31 AM	12/11/22 6:52 AM	20	188
5	12/11/22 6:43 AM	12/11/22 7:55 AM	3	191
6	12/11/22 7:07 AM	12/11/22 8:32 AM	1	172

[Traceout]

@ChrisMendoza I know I already marked this one as solved but I ran into an issue by adding on another layer. Lets say each ID is assigned a zone and I create a splicer based on zone. Is there a way to have the column and/or the measure to update based on the zone selection in the visual?

[ChrisMendoza]

@Traceout - to be honest I cannot see the logic to accomplish this.

[ChrisMendoza]

@Traceout - Yes, please explain the logic as @Mikelytics noted. Otherwise the measure can be as:

[Mikelytics]

@Traceout 

What is the business logic for these two values?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Traceout]

@Mikelytics @ChrisMendoza 

It's a running total of the customers impacted. If the end time of customers affected is past they don't get included in the 'Rolling Customers Impacted' 

So ID 4s Rolling Customers Impacted is 188 because y ou no longer include the 12 from ID 2 since ID 4's start date is after the end date of ID 2. 

[Mikelytics]

Hi @Traceout 

It was tricky but I think I got it. 🙂

Rolling Sum | Start Date End Date = 
    var __CurrentStartDate = MAX([Start_Date])
    var __CurrentEndDate = MAX([End_date])
    var __CurrentID = MAX([ID])

Return
CALCULATE(
    SUMX(
        'SampleData',
        var __IterationStartDate = [Start_Date]
        var __IterationEndDate = [End_date]
        Return
        IF(
            AND(__IterationStartDate >= __CurrentStartDate,__IterationStartDate <= __CurrentEndDate) ||
            AND(__IterationEndDate >= __CurrentStartDate,__IterationEndDate <= __CurrentEndDate) ||
            AND(__IterationStartDate <= __CurrentStartDate,__IterationEndDate >= __CurrentEndDate)
            ,[Customers],0
        )

    )
    ,ALL('SampleData'),
    'SampleData'[Start_Date]<=__CurrentStartDate
)

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics]

@Traceout 

while I was working on my complicated solution @ChrisMendoza got a much simpler one. I first thought it would not cover all cases but I think indeed his one is a complete one. So my solution works but I would go with the one of Chris 😄

Best regards

Michael

[ChrisMendoza]

@Traceout -
looks like this works:

Rolling Customers Impacted = 
VAR currentStartDate = SELECTEDVALUE('Table Data'[Start_Date])
RETURN
    CALCULATE(
        SUM('Table Data'[Customers]),
        ALL('Table Data'),
        'Table Data'[Start_Date] <= currentStartDate,
        'Table Data'[End_date] >= currentStartDate
    )

[Traceout]

@ChrisMendoza 

I may be doing something wrong here. I tried adding it in but all of the rows are blank. I selected 'new column' on the ribbon. 

[ChrisMendoza · Accepted solution]

@Traceout - I did as a measure. If you wanted as a Column then you'd need to change to:

Column Rolling Customers Impacted = 
VAR currentStartDate = 'Table Data'[Start_Date]
RETURN
    CALCULATE(
        SUM('Table Data'[Customers]),
        ALL('Table Data'),
        'Table Data'[Start_Date] <= currentStartDate,
        'Table Data'[End_date] >= currentStartDate
    )

[Traceout]

Worked perfectly! Thank you! @ChrisMendoza and thank you as well @Mikelytics