cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors