Forum Discussion
Responsive ResidentCalculate Year over Year Count
Hello,
I have the following dataset
Clients Table
ClientID, MonthsActive, YearsActive, IsTerminated, TotalLives, SizeBand, TerminationDate, EnrollmentDate.
If the client is still active the IsTerminated would be false and TerminationDate would be blank.
Date Table - which is joined to Clients table on Enrollment date using one-many relationship.
I am looking to calculate the count of Clients Year over Year. For example, for the year 2020 total number of rows in Clients are 20. 15 of them were enrolled in 2019, 2 in 2020, and the 3 are terminated. For the year 2021, we got 5 more clients, but lost 1, so I am looking to see the count of clients year over year. Condition can be Errolled date is before the year passed and if they are terminated, termination date is greater than the passed year so the client can be accounted for that year.
Thank you,
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.
3 Replies
- danextian
Super User
Hi AbhinavJoshi
As always, please post a sample data and your expected result from that. Sample data should be workable (not a screenshot) and a representation of your actual data (not overly simplified). Please refer to this stick post:
AbhinavJoshiHello 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!
- danextian
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.