Calculate active contracts based on start and end date

[Anonymous]

Hello,

My Data looks something like this:

ContractID	Start Date	End Date
1	01.01.2020	23.03.2020
2	15.02.2020	29.07.2020
3	06.06.2020	null

The last contract would be still active. I have a DateTable with the Start Date as the active relationship.

I need the end result too look like this:

Date	Active Contracts
Jan	1
Feb	2
Mar	2
Apr	1
May	1
Jun	2

How should the measure look like?

Thanks in advance!

[Anonymous]

// Here's a simple and fast solution.
// Let's say that you have a calendar
// that covers the years of the contracts.
// You can easily create a bridge table
// that will have the following fields:
// ContractID|ContractDate
// where ContractDate will be each and
// every day on which the contract is
// considered active. This is how you
// expand the intervals [Start Date, End Date].
// If the end date in null/blank, you just
// include each day from the start day
// to the very last day of the calendar
// in your model. Once you have the table
// you join Contracts on [ContractID] to it
// and your Calendar on [Date]. Now, the
// measure you're after is:

[# Active Contracts] =
    DISTINCTCOUNT( ContractDateBridge[ContractID]

// Yes, it's so easy 😉 Compare this to the
// formula given by @wdx223_Daniel...

[Anonymous]

It's not really possible to give a correct formula as long as you don't define what it means for a contract to be active within a period of time. Also, for the sake of performance and ease you should not leave BLANKs/NULLs in the date cells. It's much better to mark a non-existent end date as a date far into the future (e.g., 3000-01-01) and not join any date dimension to it (so that no BLANK rows appear in them).

By the way, is it true that a contract is considered active within a period of time [a, b] iff the interval has a non-empty intersection with the duration of the contract? Is it the right definition?

[wdx223_Daniel · Accepted solution]

Active Contacts :=
VAR _MaxDate =
    MAX ( Dates[Date] )
VAR _MinDate =
    MIN ( Dates[Date] )
VAR _StartsBeforeMonthEnd =
    CALCULATETABLE ( VALUES ( Data[ContractID] ), Dates[Date] <= _MaxDate )
VAR _notEndAfterMonthEnd =
    CALCULATETABLE (
        VALUES ( Data[ContractID] ),
        Dates[Date] >= _MinDate
            || ISBLANK ( Dates[Date] ),
        USERELATIONSHIP ( Dates[Date], Data[End Date] )
    )
RETURN
    COUNTROWS ( INTERSECT ( _notEndAfterMonthEnd, _StartsBeforeMonthEnd ) )

i created two relationships between date and start date, end date. 

hope this can help you

[amitchandak]

@Anonymous , I have a blog on similar data, see if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970