cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Graph to show Tenant continuing to age over last 3 years

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

6 REPLIES 6
Super User

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 %)?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

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+
Resolver I

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

Resolver I

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+
Resolver I

Sample visuals of the issue

Super User

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.