Forum Discussion
Calculate active contracts based on start and end date
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!
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
4 Replies
- amitchandak
Super User
Anonymous , I have a blog on similar data, see if that can help
- wdx223_Daniel
Community Champion
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
- 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? // 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...
