Graph to show Tenant continuing to age over last 3 years

[StevenHarrison]

Hi Guys - in Power BI, how can I show, using age bins (or not), customer age change over the last 3 years? i.e. if they were 24 in 2019 and 25 in 2020 where the age bins are 16-24 and 25-43 etc to display on a bar chart.

I need to have the 3 years side by side in the but the age bins stacked.

Similar to the image below:

I have tried to derive the separate age range change using:
Age 3 years ago = DATEDIFF([D-O-B], DATE(YEAR(TODAY())-3,1,1), YEAR)
Age 2 Years ago = DATEDIFF([D-O-B], DATE(YEAR(TODAY())-2,1,1), YEAR)
Age 1 year ago = DATEDIFF([D-O-B], DATE(YEAR(TODAY())-1,1,1), YEAR)

and age ranges as follows, as an example:
Person Age 3 years ago Range =
SWITCH(
TRUE(),
'EDI Tenant Data'[Age 3 years ago] < 0, "Unknown",
'EDI Tenant Data'[Age 3 years ago] < 16, "0 - 15",
'EDI Tenant Data'[Age 3 years ago] < 31, "16 - 30",
'EDI Tenant Data'[Age 3 years ago] < 46, "31 - 45",
'EDI Tenant Data'[Age 3 years ago] < 61, "46 - 60",
'EDI Tenant Data'[Age 3 years ago] < 76, "61 - 75",
'EDI Tenant Data'[Age 3 years ago] < 91, "76 - 90",
"90+"
)

Any help much appreciated thank you

[Ashish_Mathur]

Hi,

I am not sure but i can try.  Share the download link of the PBI file.  Also, shouldn't the Y axis show numbers (rather than %)?

[StevenHarrison]

Hi @Ashish_Mathur  - some DAX I have used to get the following separate charts:

Age 1 year ago = DATEDIFF([D-O-B], DATE(YEAR(TODAY())-1,1,1), YEAR)

Age 2 Years ago = IF([Tenancy Age Range] = "3 Years+" || [Tenancy Age Range] = "2 Years", DATEDIFF([D-O-B], DATE(YEAR(TODAY())-2,1,1), YEAR), 0)

Age 3 years ago = IF([Tenancy Age Range] = "3 Years+", DATEDIFF([D-O-B], DATE(YEAR(TODAY())-3,1,1), YEAR), 0) 

Tenancy Age Range = 

    SWITCH(

        TRUE(),

       'EDI Tenant Data'[TNCY-Age-Years] <= 1, "1 Year",

        'EDI Tenant Data'[TNCY-Age-Years] >= 2 && 'EDI Tenant Data'[TNCY-Age-Years] < 3, "2 Years",

        'EDI Tenant Data'[TNCY-Age-Years] >= 3, "3 Years+"

           )

Is there a way to combine the charts into one visual - X axis to show Age ranges with each year? Similar to below, but I'm missing the totals, i.e. 1 year should contain the count for the 1, 2 and 3 Years+, whereas 2 Year should contain 2 and 3 Years+ and 3 Years+ just the count of 3 Years+

[StevenHarrison]

Hi @Ashish_Mathur  - a bit more context on this, I can easily get the ages as of now, as you might expect:

is there a way to extrapolate the ages over the last 3 years and display side by side on the bar chart.

I have some calculated columns with this data in but wondered if there is a better way to go about it:

it also depends on when the Tenant began their Tenancy, so there is a Tenancy start date [TNCY-START], so if they have been with us for only 2 years say, then I only need 2 years worth of ages for the visual, if that makes sense.

any help much appreciated.

Regards

Steve

[StevenHarrison]

Hi @Ashish_Mathur  - thanks for replying, it's tricky to upload the pbi file as it contains tenant details, so I have uploaded an anonymised excel table at the bottom of this post with a sample of age related data.

Trying to explain the issue more completely, please see below:

Tenancy start dates could be any time but the Report visual requirement is to show the last 3 years of Tenant (Person) age change.

So for Tenancies 3 years and over - age can be extrapolated using calculated columns for the last 3 year period.

Age 3 years ago = DATEDIFF([D-O-B], DATE(YEAR(TODAY())-3,1,1), YEAR)
Age 2 Years ago = DATEDIFF([D-O-B], DATE(YEAR(TODAY())-2,1,1), YEAR)
Age 1 year ago = DATEDIFF([D-O-B], DATE(YEAR(TODAY())-1,1,1), YEAR)

For tenancies 2 years old and 1 year(and less than) age range needs to be extracted in a similar way but there won't be the full 3 years of data, as the Tenant hasn't been with us for the whole of the 3 years.

also for all three periods obviously Tenants are aging so this change needs to be reflected in the age range for each of the 3 years

'EDI Tenant Data'[Age 3 years ago] < 0, "Unknown",
'EDI Tenant Data'[Age 3 years ago] < 16, "0 - 15",
'EDI Tenant Data'[Age 3 years ago] < 31, "16 - 30",
'EDI Tenant Data'[Age 3 years ago] < 46, "31 - 45",
'EDI Tenant Data'[Age 3 years ago] < 61, "46 - 60",
'EDI Tenant Data'[Age 3 years ago] < 76, "61 - 75",
'EDI Tenant Data'[Age 3 years ago] < 91, "76 - 90",
"90+"

PERSON-REF	Full Name	D-O-B	Person Age	Person Age Range	Person Age (bins)	Person Age 1 year ago	Person Age 1 year ago Range	Person Age 2 Years ago	Person Age 2 years ago Range	Person Age 3 years ago	Person Age 3 years ago Range	TENANCY-REF	TNCY-STATUS	ON-TNCY	TNCY-START-Date	TNCY-Age-Years	Tenancy Age Range
1	Mr John Smith	12/09/1945 00:00	78	76 - 90	75	77	76 - 90	76	76 - 90	75	61 - 75	FZIR212222224	CURRENT	TRUE	26/04/2021 00:00	2	2 Years
2	Mr Jordan Smith	20/08/1998 00:00	25	16 - 30	15	24	16 - 30	23	16 - 30	22	16 - 30	SLED212162222	CURRENT	TRUE	06/07/2020 00:00	3	3 Years+
3	Mr Lee Smith	28/06/1985 00:00	38	31 - 45	30	37	31 - 45	36	31 - 45	35	31 - 45	LZNE242192223	CURRENT	TRUE	16/12/2013 00:00	9	3 Years+
4	Mr Wal Smith	31/12/1979 00:00	43	31 - 45	30	43	31 - 45	42	31 - 45	41	31 - 45	ZYRE222882224	CURRENT	TRUE	10/02/2020 00:00	3	3 Years+
5	Mr Assah Smith	18/04/1969 00:00	54	46 - 60	45	53	46 - 60	52	46 - 60	51	46 - 60	MONT212122222	CURRENT	TRUE	10/11/2014 00:00	8	3 Years+
6	Mrs Abdi Smith	26/12/1991 00:00	31	31 - 45	30	31	31 - 45	30	16 - 30	29	16 - 30	ESHE212262226	CURRENT	TRUE	05/12/2022 00:00	0	<= 1 Year
7	Mr Basim Smith	21/10/1964 00:00	59	46 - 60	45	58	46 - 60	57	46 - 60	56	46 - 60	LZUR212522226	CURRENT	TRUE	20/09/2021 00:00	2	2 Years
8	Mrs Fred Smith	20/09/1995 00:00	28	16 - 30	15	27	16 - 30	26	16 - 30	25	16 - 30	WENT212122224	CURRENT	TRUE	04/11/2019 00:00	3	3 Years+
9	Mr Khaled Smith	15/12/1972 00:00	50	46 - 60	45	50	46 - 60	49	46 - 60	48	46 - 60	STZM212242224	CURRENT	TRUE	08/09/2014 00:00	9	3 Years+
10	Mrs Karim Smith	24/12/1983 00:00	39	31 - 45	30	39	31 - 45	38	31 - 45	37	31 - 45	WZRW212492223	CURRENT	TRUE	20/08/2018 00:00	5	3 Years+
11	Mr Bert Smith	01/07/1977 00:00	46	46 - 60	45	45	31 - 45	44	31 - 45	43	31 - 45	GRZN211522224	CURRENT	TRUE	03/06/2019 00:00	4	3 Years+
12	Mrs Sha Smith	20/02/1983 00:00	40	31 - 45	30	39	31 - 45	38	31 - 45	37	31 - 45	DIXO212182226	CURRENT	TRUE	18/06/2018 00:00	5	3 Years+
13	Mr Anlo Smith	30/10/1971 00:00	52	46 - 60	45	51	46 - 60	50	46 - 60	49	46 - 60	PION222572221	CURRENT	TRUE	10/04/2023 00:00	0	<= 1 Year
14	Mr Mike Smith	01/01/1984 00:00	39	31 - 45	30	38	31 - 45	37	31 - 45	36	31 - 45	ELDO212422224	CURRENT	TRUE	06/08/2018 00:00	5	3 Years+
15	Mr Merlin Smith	02/03/1965 00:00	58	46 - 60	45	57	46 - 60	56	46 - 60	55	46 - 60	GRZN222972222	CURRENT	TRUE	02/08/2021 00:00	2	2 Years
16	Mr Janet Smith	27/02/1992 00:00	31	31 - 45	30	30	16 - 30	29	16 - 30	28	16 - 30	GRZN222932223	CURRENT	TRUE	02/11/2020 00:00	2	2 Years
17	Miss Victoria Smith	08/01/1991 00:00	32	31 - 45	30	31	31 - 45	30	16 - 30	29	16 - 30	GRZN222832222	CURRENT	TRUE	05/10/2015 00:00	8	3 Years+
18	Miss Yasmine Jones	07/07/1989 00:00	34	31 - 45	30	33	31 - 45	32	31 - 45	31	31 - 45	GRZN222612221	CURRENT	TRUE	11/07/2005 00:00	18	3 Years+
19	Mrs Adam Jones	05/03/1976 00:00	47	46 - 60	45	46	46 - 60	45	31 - 45	44	31 - 45	STZT222142224	CURRENT	TRUE	17/11/2014 00:00	8	3 Years+

[StevenHarrison]

Sample visuals of the issue

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523