Forum Discussion
Dynamic Age Groups - DAX
Hi,
I am trying to represent the age demographic(with dynamic groups) across a workforce in DAX and I am having some trouble trying to represent it in the form of a graph.
I have the following data:
- Date Table
- Employee Info
- Hire date
- Date of Birth
- Termination Date
| ID | Date of Birth | Hire Date | Termination Date |
| 1 | 30/3/2002 | 3/4/2013 | |
| 2 | 30/5/1990 | 3/4/2014 | 5/3/2020 |
3. Support Age Table
| Age Bracket | Sort Order | Min | Max |
| Under 18 | 1 | 1 | 17.999999999 |
| 18-24 | 2 | 18 | 24.9999999 |
| 25-34 | 3 | 25 | 34.999999999 |
I have tried using a calculated column but realised that the age needs to be calculated based on the date selected, as opposed to the today's date.
How would I use a DAX calculation to calculate the age of active employees and then to represent it in an easy to read bar graph, with each category representing an age bracket?
Hi Anonymous ,
Try this measure.
Count = SWITCH ( MAX ( 'Table (2)'[Age Bracket] ), "18-24", CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', YEAR ( TODAY () ) - 24 <= YEAR ( [Date of Birth] ) && YEAR ( [Date of Birth] ) <= YEAR ( TODAY () ) - 18 ) ), "25-34", CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', YEAR ( TODAY () ) - 34 <= YEAR ( [Date of Birth] ) && YEAR ( [Date of Birth] ) <= YEAR ( TODAY () ) - 25 ) ), "Under 18", CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', YEAR ( TODAY () ) - 18 >= YEAR ( [Date of Birth] ) ) ) )Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , New mewasure
age =
Var _1 = if(isbalnk(max(Table[Termination Date]) ,selectedvalue(Date[Date]), min(selectedvalue(Date[Date]),Table[Termination Date]))
return
datediff([Hire Date], _1,month)/12Create an independent sequence table
New Table = addcolumns(generateseries(1,10,1), "end", [Value]+.999999)
Not this will give range , create measures help from this table
refer how to do using my video : https://youtu.be/CuczXPj0N-k
or
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualizationHi, Anonymous
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
all measures are in the sample pbix file.
https://www.dropbox.com/s/w3sl3bhq3f8utzh/leont3.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Hi Anonymous ,
Try this measure.
Count = SWITCH ( MAX ( 'Table (2)'[Age Bracket] ), "18-24", CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', YEAR ( TODAY () ) - 24 <= YEAR ( [Date of Birth] ) && YEAR ( [Date of Birth] ) <= YEAR ( TODAY () ) - 18 ) ), "25-34", CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', YEAR ( TODAY () ) - 34 <= YEAR ( [Date of Birth] ) && YEAR ( [Date of Birth] ) <= YEAR ( TODAY () ) - 25 ) ), "Under 18", CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', YEAR ( TODAY () ) - 18 >= YEAR ( [Date of Birth] ) ) ) )Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
