Running total for rows with start and end date columns

[KasperWF]

Hi Power BI Community!

I'm new to Power BI, but have created a few reports already. I have been looking around at various methods for creating running totals for sales, hours spend, etc. The problem is that most of them seem to focus on single values, whereas I am looking for something that takes a "subscription" with a start and end date into account. 

This is an example of how the data is structured (some columns left out). When a subscription is active, it has a start date, and the end date is NULL, when the subscription is cancelled, a subscription end date is set.

ID	StartDate	EndDate
1	2018-05-04 12:55:49.0000000	2018-06-07 09:53:39.0000000
2	2018-07-09 10:07:14.0000000	NULL

So basically, I am trying to build a running total where on the date 2018-05-04 it will increment by 1, and on 2018-06-07 it will decrement by 1. 

I hope someone can help me out as I am unsure how to do this.

[KasperWF]

Thanks to everyone in the thread. I will try the suggestions and mark the question as answered when I have tried to run the measures.

[V-lianl-msft]

Hi @KasperWF ,

First create an unrelated calendar table.

Then create a measure like this:

measure =
CALCULATE (
    COUNTX (
        FILTER (
            'Table',
            'Table'[StartDate] <= MAX ( 'Table 2'[Date] )
                && (
                    ISBLANK ( 'Table'[EndDate] )
                        || 'Table'[EndDate] > MAX ( 'Table 2'[Date] )
                )
        ),
         ( 'Table'[ID] )
    )
)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KasperWF , refer if my blog can help on start & end date usages

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[TomMartens · Accepted solution]

Hey @KasperWF ,

the challenge you are facing has a name, it's called event-in-progress.

This blog by Gerhard Brueckl is a great read, and also links to all the other important articles dedicated to this challenge.

I recommend to start with the article by Jason Thomas.

Hopefully, this provides the ideas you need to tackle the challenge.

Regards,

Tom