Skip to main content
cancel
Showing results for 
Search instead 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

Reply
StevenHarrison
Resolver I
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:
Age bins.jpg

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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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

 

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+
age bar charts2.jpg

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

ages now.PNG

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:

columns.PNG

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

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-REFFull NameD-O-BPerson AgePerson Age RangePerson Age (bins)Person Age 1 year agoPerson Age 1 year ago RangePerson Age 2 Years agoPerson Age 2 years ago RangePerson Age 3 years agoPerson Age 3 years ago RangeTENANCY-REFTNCY-STATUSON-TNCYTNCY-START-DateTNCY-Age-YearsTenancy Age Range
1Mr John Smith12/09/1945 00:007876 - 90757776 - 907676 - 907561 - 75FZIR212222224CURRENTTRUE26/04/2021 00:0022 Years
2Mr Jordan Smith20/08/1998 00:002516 - 30152416 - 302316 - 302216 - 30SLED212162222CURRENTTRUE06/07/2020 00:0033 Years+
3Mr Lee Smith28/06/1985 00:003831 - 45303731 - 453631 - 453531 - 45LZNE242192223CURRENTTRUE16/12/2013 00:0093 Years+
4Mr Wal Smith31/12/1979 00:004331 - 45304331 - 454231 - 454131 - 45ZYRE222882224CURRENTTRUE10/02/2020 00:0033 Years+
5Mr Assah Smith18/04/1969 00:005446 - 60455346 - 605246 - 605146 - 60MONT212122222CURRENTTRUE10/11/2014 00:0083 Years+
6Mrs Abdi Smith26/12/1991 00:003131 - 45303131 - 453016 - 302916 - 30ESHE212262226CURRENTTRUE05/12/2022 00:000<= 1 Year
7Mr Basim Smith21/10/1964 00:005946 - 60455846 - 605746 - 605646 - 60LZUR212522226CURRENTTRUE20/09/2021 00:0022 Years
8Mrs Fred Smith20/09/1995 00:002816 - 30152716 - 302616 - 302516 - 30WENT212122224CURRENTTRUE04/11/2019 00:0033 Years+
9Mr Khaled Smith15/12/1972 00:005046 - 60455046 - 604946 - 604846 - 60STZM212242224CURRENTTRUE08/09/2014 00:0093 Years+
10Mrs Karim Smith24/12/1983 00:003931 - 45303931 - 453831 - 453731 - 45WZRW212492223CURRENTTRUE20/08/2018 00:0053 Years+
11Mr Bert Smith01/07/1977 00:004646 - 60454531 - 454431 - 454331 - 45GRZN211522224CURRENTTRUE03/06/2019 00:0043 Years+
12Mrs Sha Smith20/02/1983 00:004031 - 45303931 - 453831 - 453731 - 45DIXO212182226CURRENTTRUE18/06/2018 00:0053 Years+
13Mr Anlo Smith30/10/1971 00:005246 - 60455146 - 605046 - 604946 - 60PION222572221CURRENTTRUE10/04/2023 00:000<= 1 Year
14Mr Mike Smith01/01/1984 00:003931 - 45303831 - 453731 - 453631 - 45ELDO212422224CURRENTTRUE06/08/2018 00:0053 Years+
15Mr Merlin Smith02/03/1965 00:005846 - 60455746 - 605646 - 605546 - 60GRZN222972222CURRENTTRUE02/08/2021 00:0022 Years
16Mr Janet Smith27/02/1992 00:003131 - 45303016 - 302916 - 302816 - 30GRZN222932223CURRENTTRUE02/11/2020 00:0022 Years
17Miss Victoria Smith08/01/1991 00:003231 - 45303131 - 453016 - 302916 - 30GRZN222832222CURRENTTRUE05/10/2015 00:0083 Years+
18Miss Yasmine Jones07/07/1989 00:003431 - 45303331 - 453231 - 453131 - 45GRZN222612221CURRENTTRUE11/07/2005 00:00183 Years+
19Mrs Adam Jones05/03/1976 00:004746 - 60454646 - 604531 - 454431 - 45STZT222142224CURRENTTRUE17/11/2014 00:0083 Years+

Sample visuals of the issue
EDI age visuals.jpg

lbendlin
Super User
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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors