topic Finding out if a contract is active or not based on dynamic date filter in DAX Commands and Tips

Finding out if a contract is active or not based on dynamic date filter

idkhonestlyanyt — Wed, 19 Feb 2025 21:12:08 GMT

So I have my datetable which contains the columns: Date, MonthYear, Fiscal Year, Fiscal Quarter.

I have my contract table which contains:
Contract ID, StartDate, EndDate.

The two are not connected. I want to find out the number of active contracts based on the date slicer. Example, there is a contract that started on 9th Feb, 2020 and ends on Feb 30, 2027. If I select the option on MonthYear column: June, 2024, this contract should count as 1. How do I go about this? The next part was trying to find out the new contracts added since last month based on the date selected and the contracts expired based in the last month based on the date selected.

Is this possible?

Example data:

Contract ID	RelationshipStartDate	RelationshipEndDate
786	2020-02-07 0:00	2025-10-21 0:00
1078	2020-02-07 0:00	2025-08-21 0:00
1316	2021-08-18 0:00	2025-11-13 0:00
1720	2020-02-07 0:00	2025-03-25 0:00
3042	2020-02-07 0:00	2025-03-06 0:00
3581	2023-01-12 0:00	2025-05-08 0:00
9549	2020-02-07 0:00	2026-01-09 0:00
12150	2020-02-07 0:00	2025-06-07 0:00
13353	2020-02-07 0:00	2025-03-05 0:00
13376	2020-02-07 0:00	2025-12-13 0:00
14945	2020-02-07 0:00	2026-01-11 0:00
18525	2021-08-23 0:00	2025-08-21 0:00
21745	2020-02-07 0:00	2025-07-11 0:00
21981	2020-02-07 0:00	2025-03-17 0:00
30658	2020-02-07 0:00	2025-09-26 0:00
33396	2020-02-07 0:00	2026-01-06 0:00
36055	2020-02-07 0:00	2025-04-29 0:00

Re: Finding out if a contract is active or not based on dynamic date filter

Irwan — Wed, 19 Feb 2025 22:57:04 GMT

hello idkhonestlyanyt

the expected value for June,2024 is 1, but i dont see any data in 2024 in your sample data.

is this the result of the sample data given above?

Thank you

Re: Finding out if a contract is active or not based on dynamic date filter

divyed — Thu, 20 Feb 2025 06:23:42 GMT

Hello idkhonestlyanyt ,

How did you calculate your output ? Please share data to support your output. Also You have passed Feb 30 2027 as input which is not valid. Kindly share relevant data and ouput to ceck further.

Meanwhile you can try few things like

Active Contracts =
VAR SelectedDate = MAX(DateTable[Date])
RETURN
CALCULATE(
COUNT(Contracts[Contract ID]),
Contracts[StartDate] <= SelectedDate,
Contracts[EndDate] > SelectedDate
)

New Contracts =
VAR SelectedMonth = MAX(DateTable[MonthYear])
VAR PreviousMonth = FORMAT(EDATE(MAX(DateTable[Date]), -1), "YYYY-MM")
RETURN
CALCULATE(
COUNT(Contracts[Contract ID]),
FORMAT(Contracts[StartDate], "YYYY-MM") = PreviousMonth
)

Expired Contracts =
VAR SelectedMonth = MAX(DateTable[MonthYear])
VAR PreviousMonth = FORMAT(EDATE(MAX(DateTable[Date]), -1), "YYYY-MM")
RETURN
CALCULATE(
COUNT(Contracts[Contract ID]),
FORMAT(Contracts[EndDate], "YYYY-MM") = PreviousMonth
)

I hope this helps.

Warm Regards,