Visualize ages

[Anonymous]

Ive done some searching and cant quite find the answer to this question.

i have a large table. One of the fields has a "first found" date with is a date in the past.  Could be 1 day ago could 1000 days ago.  I have duplciated this column and converted it to age so i have a column shwoing the age which is essnetially thenumber of days between the "first found" and today.  Age, simple.

Now i want to visualsie on a single graph (probably just a bar graph) a total value for the following

30-60 days old

60-180 days old

180-365 days old

365+ days old

I can do this with filters simple enough if i do seperate visuals but can tfigure out how to do it on one visual.  Any ideas?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download example PBIX file with all the data and charts in this answer.

I did this in Power Query but you could also do it in DAX if you like.  I find PQ a bit easier for this.

This is the table I end up with

Here's the query code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJSitUhgmOMjWOMzDFF5lhSlWNoQCbPECsvFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Found" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Found", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Age", each Duration.TotalDays(Date.From(DateTime.LocalNow()) - [First Found])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Age", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Age Bands", each if [Age] < 30 then "1-29" else 

if [Age] < 60 then "30-59" else 

if [Age] < 180 then "60-179" else 

if [Age] < 365 then "180-364" else 

"365+"),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Age Bands"}, #"Sort Order", {"Age Band"}, "Sort Order", JoinKind.LeftOuter),
    #"Expanded Sort Order" = Table.ExpandTableColumn(#"Merged Queries", "Sort Order", {"Sort Order"}, {"Sort Order"})
in
    #"Expanded Sort Order"

What I've done is create Custom Columns for Age and Age bands.  The Sort Order column comes from another table specfically created just so you can sort the Age Bands in a pre-determined way.  If you tried sorting by the Age Bands text values, you'd get undesirable results.  This is the table

You'll see that the Age Bands don't overlap, i.e. you can't have bands like 30-60 and 60-180.  A value of 60 can't be in 2 bands.

Here's the bar chart

It's sorted on the Age Bands but they have a Sort By column which is the Sort Order column - read more on this here Sort a Column with a Custom Order in Power BI - RADACAD

Regards

Phil