Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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...
Need Help
  • jahida's avatar
    jahida
    10 years ago

    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's avatar
Anonymous
Not applicable
10 years ago

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's avatar
jahida
Icon for Impactful Individual rankImpactful Individual
10 years ago

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's avatar
    Anonymous
    Not applicable
    10 years ago

    Amazing! jahida

     

    You're the best, thank you.