Forum Discussion
Histogram using birthday dates
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?
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'])))
4 Replies
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.
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.
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'])))