Histogram using birthday dates

[Anonymous]

Hello,

I have a column of dates (birthdays), and I want to create a histogram showing counts within an age range, for example

01-02

03-05

06-12
13-17
18-21
22-30
31-40
41-50
51-65
66-75
75 +

Ive tried using the Histogram visual, but this seems problematic as it does not allow me to standardize the age ranges as described above.  Any ideas?

[jahida]

One possible solution would be to make a second table (say, 'HistAges').

3 columns: Range Visual (formatted like you listed below, or however else you want), Lower Bound, Upper Bound.

Then you can make a Measure to count birthdays in each range. Maybe:

Count = CALCULATE(COUNTROWS('Table'), DATEDIFF('Table'[Date], TODAY(), YEAR) >= MIN('HistAges'[Lower Bound]), DATEDIFF('Table'[Date], TODAY(), YEAR) <= MAX('HistAges'[Upper Bound]))

You can then graph Range Visual against the measure to get your desired result. Too lazy to test, so sorry if there are syntax errors.

[Anonymous]

thanks @jahida,

I created the new table as described.  And have the following measure...

CountDOB = CALCULATE(COUNTROWS(Sheet1), DATEDIFF(Sheet1[DOB], TODAY(), YEAR) >= MIN('HistAges'[AgeLowerBound]), DATEDIFF(Sheet1[DOB], TODAY(), YEAR) <= MAX('HistAges'[AgeUpperBound]))

Sheet1[DOB] = Birthday in date format

HistAges'[AgeLowerBound] = column within the new table for lower age boundry

'HistAges'[AgeUpperBound] = column within new table for upper age boundry

However the measure returns the following error. "A function 'MIN' has been used in a True/False expression that is used as a table filter expression. This is not allowed"

Any ideas?  thanks in advance for your help.

[jahida · Accepted solution]

Oh yeah my bad, forgot that was illegal. How's:

CountDOB = COUNTROWS(FILTER(Sheet1, DATEDIFF(Sheet1[DOB], TODAY(), YEAR) >= MIN('HistAges'[AgeLowerBound]) && DATEDIFF(Sheet1[DOB], TODAY(), YEAR) <= MAX('HistAges'[AgeUpperBound'])))

[Anonymous]

Amazing! @jahida

You're the best, thank you.