Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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
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