Count active contracts in time period


I have what I thought was a simple DAX formula but I am not getting this right. Hopefully someone can point me in the right direction

I need a measure that counts hte number of active contracts for a given time period.

A simple data model with contracts containing ContractId, StartDate, EndDate. StartDate is the start of the contract and EndDate is when the contract expires. If EndDate is null/blank the contract is active.

StartDate is always set to a value, which is set when the contract is created.

I also have a generic date/calendar table. There is an active relationship between Contract[StartDate] and Calendar[Date] and an inactive relationship between Contract[EndDate] and Calendar[Date]
I want to use this in a report with a date slicer with Calendar[Date] that allow me to select the time period for which I am interested. Again, there is an active realtionship between Contract[StartDate] and Calendar[Date]

Example data

If I were to set the time period for 2019 I should get 2 as the result (row 1 and 2)
If 2020 4 (rows 2-5)
If 2021 2 (rows 4-5)

The T-SQL that extracts the correct number of rows from e.g. 2018 would look like this.
Select Count(1) from Contract c where c.StartDate <= ‘2018-12-31 23:59:59’ and (EndDate > ‘2018-12-31 23:59:59’ or EndDate is null)
So all contracts that were startdate is before the end of the time period and ends after the time period or is null (no termination set)

I am thinking that I must bypass the relationship between Contract[StartDate] and Calendar[Date] as I am interested in Contracts that was created on or before the currently selected time period.

I was thinking something like:

Active Contracts = CALCULATE(COUNTROWS(Contracts);Contract[StartDate]<=FIRSTPERIOD(Contract[Date]);OR(Contract[EndDate]>LASTDATE(Calendar[Date]);ISBLANK(Agreement[ClosedDate]);

But it give me all kind of syntax errors. Does anyone have an idea on where to start on this?



After some more experimenting I have successfully calculated the total active contracts using this DAX formula.

Active Contracts = CALCULATE(COUNTROWS(FILTER(ALL(Contract); Contract[StartDate]<=max(Calendar[Date]) && (Contract[EndDate] > MAX(Calendar[Date]) ||ISBLANK(Contract[EndDate])))))

This gives me the correct total for the time period.
However that does not work when I add it to a matrix to break it down per country and project as per this screenshot. I have experimented with ALLEXCEPT to no avail. Please advice


You need to have 2 inactive relationships.

You can create a second datetable, one for the events (copy of the standard date table) and one “standard date table” that you make active, otherwise other measures need to have USERELATIONSHIP.



Hi Paul!

Wow, thanks a lot. I tried to inactivate the relationship and got it to work then. Obvious drawback is that I have to alter all other measure to use USERELATIONSHIP which makes them unnecessarily complex.

I don´t quite follow the other approach with creating a second date table. Are you saying to create a duplicate of the date table and then make active relationships between Contract[StartDate] and Calendar[Date] and another active relationship between Contract[EndDate] and CalendarEndDate[Date] or what?


see example.
Create a new table in PBI
Events Date = [name of your date table], now for the inactive relation ships use the Events Date table and for the other measure use the standard date table.



Thanks Paul, that makes sense. I will give it a go. This membership really paying off! :smiley:

Should work, membership is an asset, recommend to follow this forum too, so much to learn from other people’s questions and models.