Forum Discussion
Responsive ResidentCalculate Year over Year Count
Hi AbhinavJoshi ,
Create a separate dates table:
Dates = ADDCOLUMNS ( CALENDAR ( DATE ( 2007, 1, 1 ), TODAY () ), "Year", YEAR ( [Date] ), "YYYYMM", FORMAT ( [Date], "YYYYMM" ) )Link this to Enrollment date (active relationship) and Termination Date (inactive) usingi a one to many single direction relationship.
Create these measures:
Enrolled = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) ) ) Terminated = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) && NOT ( ISBLANK ( Dates[Date] ) ) ), USERELATIONSHIP ( Dates[Date], 'Table'[TerminationDate] ) ) Active = [Enrolled] - [Terminated]Or you can put them in one measure instead. I separate them so you can see what happens for each.
I modified your sample data a bit by adding an extra termination date for Account2. Please refer to the attached pbix for the details.
Responsive ResidentHello danextian. Please see dataset attached
| Client ID | EnrollmentDate | Lives | YearsActive | MonthsActive | SizeBand | Terminated | TerminationDate |
| Account1 | 2022-06-01 0:00 | 1 | 2.001368925393566 | 24 | 1: 3 - 10 | FALSE | |
| Account2 | 2007-03-01 0:00 | 7 | 17.253935660506503 | 207 | 1: 3 - 10 | FALSE | |
| Account3 | 2010-11-01 0:00 | 110 | 13.582477754962355 | 163 | 4: 101 - 200 | FALSE | |
| Account4 | 2007-03-01 0:00 | 9 | 17.253935660506503 | 207 | 1: 3 - 10 | FALSE | |
| Account5 | 2023-09-01 0:00 | 191 | 0.7501711156741958 | 9 | 4: 101 - 200 | FALSE | |
| Account6 | 2021-05-01 0:00 | 33 | 3.0855578370978782 | 37 | 2: 11 - 50 | FALSE | |
| Account7 | 2007-03-01 0:00 | 2 | 15.916666 | 191 | 1: 3 - 10 | TRUE | 2023-02-01 0:00 |
| Account8 | 2022-02-01 0:00 | 135 | 2.329911019849418 | 28 | 4: 101 - 200 | FALSE | |
| Account9 | 2022-05-01 0:00 | 5 | 2.086242299794661 | 25 | 1: 3 - 10 | FALSE | |
| Account10 | 2022-06-01 0:00 | 2 | 2.001368925393566 | 24 | 1: 3 - 10 | FALSE | |
| Account11 | 2023-03-01 0:00 | 4 | 1.2539356605065024 | 15 | 1: 3 - 10 | FALSE | |
| Account12 | 2023-07-01 0:00 | 10 | 0.919917864 | 11 | 2: 11 - 50 | FALSE | |
| Account13 | 2023-12-01 0:00 | 3 | 0.5010266940451745 | 6 | 1: 3 - 10 | FALSE | |
| Account14 | 2024-03-01 0:00 | 8 | 0.2518822724161533 | 3 | 1: 3 - 10 | FALSE | |
| Account15 | 2023-09-01 0:00 | 1 | 0.7501711156741958 | 9 | 1: 3 - 10 | FALSE | |
Here Clients Active for 2007 shoud be 3, (Account7 was terminated on 2023 so should be counted as active until then). For 2008 it should be 3, For 2009 should be 3, For 2010 should be 4, For 2021 shoud be 5, For 20202 shoud be 9, For 2023 should be 13 ( Total 14 but remove Account 7 as it got terminated that year), For 2024 shoud be 14. Thank you!
Super UserHi AbhinavJoshi ,
Create a separate dates table:
Dates =
ADDCOLUMNS (
CALENDAR ( DATE ( 2007, 1, 1 ), TODAY () ),
"Year", YEAR ( [Date] ),
"YYYYMM", FORMAT ( [Date], "YYYYMM" )
)
Link this to Enrollment date (active relationship) and Termination Date (inactive) usingi a one to many single direction relationship.
Create these measures:
Enrolled =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)
Terminated =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( Dates ),
Dates[Date] <= MAX ( Dates[Date] )
&& NOT ( ISBLANK ( Dates[Date] ) )
),
USERELATIONSHIP ( Dates[Date], 'Table'[TerminationDate] )
)
Active =
[Enrolled] - [Terminated]
Or you can put them in one measure instead. I separate them so you can see what happens for each.
I modified your sample data a bit by adding an extra termination date for Account2. Please refer to the attached pbix for the details.