Count active contracts in time period

[Frede]

Hi!

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
1;01/01/2019;12/31/2019
2;01/01/2019;12/31/2020
3;01/01/2020;12/31/2020
4;01/01/2020;12/31/2021
5;01/01/2020;null

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?

Thanks

Fredrik

[Frede]

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

[Paul]

Hi,

You need to have 2 inactive relationships.

Sales and Orders in Progress

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.

Paul

[Frede]

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?

Thanks
Fredrik

[Paul]

Hi,
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.

Paul

[Frede]

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

[Paul]

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

Paul