Hi All
I need help with an issue I am having which is a bit above my expertise to resolve. I have explained the issue below but please let me know if you need any more information. I’d appreciate any help I can get as I just can’t seem to figure it out on my own.
What I am trying to achieve
I have two tables, one with information on when a tenant moved to a property and the other on library cards that have been taken out for a period. What I need to do is to see how many library cards were taken out at the address after the individual tenancy started so that I can compare this against the amount of tenants to see if there is a difference.
What I have done
I have created a unique identifier of the 1st line of the address + the postcode. This unique identifier is what I am using to match the records.
I can count the amount of tenants in the property per the unique ID and the amount of library cards, but some of the library cards are before the tenants start date so are not valid. I have tried to use a measure using the calculate function to count the rows in the tenancy tables with a filter of the firstdate of the tenancy but it does not seem to work. My current attempt is below
Count library cards = CALCULATE (
COUNTAX('Library '[Library.registration_date],'Library '[Unique identifier] = 'Housing '[Unique identifier]),
FIRSTDATE('Housing- Library '[Tenancy Start Date]
))
My tables (example data)
Housing table
| Unique identifier | Tenancy start date | Address | Postcode | Name |
| 1 fake road a2 123 | 01/01/2016 | 1 fake road | a2 123 | John Smith |
| 1 fake road a2 123 | 01/01/2016 | 1 fake road | a2 123 | Jane Smith |
| 1 fake road a2 123 | 01/01/2016 | 1 fake road | a2 123 | Frank smith |
| 2 fake road a2 123 | 15/02/2016 | 2 fake road | a2 123 | John Doe |
Library table
| Unique identifier | Library card registered | Address | Postcode | Name |
| 1 fake road a2 123 | 05/06/2014 | 1 fake road | a2 123 | Frank Spencer |
| 1 fake road a2 123 | 07/02/2016 | 1 fake road | a2 123 | Jane Smith |
| 1 fake road a2 123 | 07/02/2016 | 1 fake road | a2 123 | Frank smith |
| 2 fake road a2 123 | 15/02/2016 | 2 fake road | a2 123 | John Doe |
| 1 fake road a2 123 | 03/05/2016 | 1 fake road | a2 123 | Jane Smith |
What I would like to create
| Unique indentifier | Count of tenancys | Count of library cards post tenancy start date | Difference |
| 1 fake road a2 123 | 3 | 3 | 0 |
Any help would be greatly appreciated
Regards
Dan
