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]
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?