Forum Discussion
NewPBIe
Helper II
Helper IINew, active and closed Contracts
Hello everybody. I've got a Date Table (Calender) and a Table with IDs, Start date and End date for some Contracts (Data).
I need to answer three different questions pro each Period (fiscal year october - september):
1. how many new contracts (in this period)
2. how many active contracts (from this and the previus periods)
3. how many closed contracts (in this period)
Now 1 and 3 are easy: I connected Calender[Date] with both [Start Date] and [End Date] from Data and selected the correct relastionship between the Tables.
I am stuck with 2, since I can' t ignore the active relationship and every measure keeps counting the new contracts and not the active ones.
Sample Data / Input:
| ID | START | END |
| A | 16.10.2020 | 30.08.2021 |
| B | 16.10.2020 | 31.01.2021 |
| C | 21.12.2020 | 31.04.2021 |
| D | 13.01.2021 | |
| E | 08.03.2021 | 30.06.2021 |
| F | 01.09.2021 | 30.09.2021 |
Output:
| New | Closed | Active | which IDs should be counted in active | |
| Okt. 20 | 2 | 0 | 2 | A;B |
| Nov. 20 | 0 | 0 | 2 | A;B |
| Dez. 20 | 1 | 0 | 3 | A;B;C |
| Jan. 21 | 1 | 1 | 4 | A;B;C;D |
| Feb. 21 | 0 | 0 | 3 | A;C;D |
| Mrz. 21 | 1 | 0 | 4 | A;C;D;E |
| Apr. 21 | 0 | 1 | 4 | A;C;D;E |
| Mai. 21 | 0 | 0 | 3 | A;D;E |
| Jun. 21 | 0 | 1 | 3 | A;D;E |
| Jul. 21 | 0 | 0 | 2 | A;D |
| Aug. 21 | 0 | 1 | 2 | A;D |
| Sep. 21 | 1 | 1 | 2 | D;F |
What am I doing wrong? Thank you in advance!
Try
Active contracts = VAR ReferenceDate = MAX ( 'Calendar'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Date' ), 'Table'[Start date] <= ReferenceDate && ( ISBLANK ( 'Table'[End date] ) || 'Table'[End date] > ReferenceDate ) ) RETURN Result
1 Reply
johnt75Super User
Try
Active contracts = VAR ReferenceDate = MAX ( 'Calendar'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Date' ), 'Table'[Start date] <= ReferenceDate && ( ISBLANK ( 'Table'[End date] ) || 'Table'[End date] > ReferenceDate ) ) RETURN Result