Forum Discussion
dynamically age group
I have a measure that calculates the age dynamically considering the date selected by the user. but I can't group the results. the simplest solution would be to calculate the age in a column calculated to today but this is no solution as my user may want to select dates in the past and the age must be in relation to the selected date
formula dax calculate age
age = VAR Birthdate = table[Date of birth]
VAR ThisDay = MAX('Calendar'[Date])
VAR IntBirthdate = YEAR ( Birthdate ) * 10000 + MONTH ( Birthdate ) * 100 + DAY ( Birthdate )
VAR IntThisDay = YEAR ( ThisDay ) * 10000 + MONTH ( ThisDay ) * 100 + DAY ( ThisDay )
VAR Age = QUOTIENT ( IntThisDay - IntBirthdate, 10000 )
VAR CheckedAge = DIVIDE ( Age, NOT ISBLANK ( Birthdate ) )
RETURN
CheckedAge
range age
| range age | order |
| <=20 | 1 |
| 21-30 | 2 |
| 31-40 | 3 |
| 41-50 | 4 |
| 51-60 | 5 |
| 60+ | 6 |
thank you very much
Anonymous ,
You need to have measure like
new measure =
var _1 = maxx(allselected('Date', 'Date'[Date])
return
datediff(min(Table[Birthdate]),_1,year)Then you need to do bucket using person/user
example
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
2 Replies
Anonymous ,
You need to have measure like
new measure =
var _1 = maxx(allselected('Date', 'Date'[Date])
return
datediff(min(Table[Birthdate]),_1,year)Then you need to do bucket using person/user
example
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kthanks amitchandak