Calculate staff pool by age

[Anonymous]

Hi,

Source is a tabell with name, staff number, birthday and position.

for example:

I would like to group them by position and age, but I want to see how this look in 3-5 years, when I know that employe retirement at the age of 70.

Thanks

[amitchandak]

@Anonymous , if you need to create a bucketing. You need to create age

Age column =

var _age = datediff ([date of birth],today(),year)

return

Switch( True(),

_age  < 25, " 0 -25",

_age  < 30, " 25 -30",

_age  < 50, " 30 -50",

_age  < 70, " 70 -70"

)

In case you need that is base on the selected date on page

Age measure = datediff ([date of birth],selectedvalue[date[date]) ,year)

Now you need have a bucket table with  limits and have one more measure with the name in the row context to handle the same

For that refer

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bins
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-in-power-bi/

https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Greg_Deckler · Accepted solution]

@Anonymous - You could create an Age column like this:

Age = 
    VAR __Age = DATEDIFF([Date of Birth],TODAY(),YEAR)
RETURN
    SWITCH(TRUE(),
        __Age>=25 && __Age<30,"25-30",
        __Age>=30 && __Age<50,"30-50",
        __Age>=50 && __Age<70,"50-70"
    )

For 3 years in the future, it would be this:

Age = 
    VAR __Age = DATEDIFF([Date of Birth],DATE(YEAR(TODAY())+3,MONTH(TODAY()),DAY(TODAY())),YEAR)
RETURN
    SWITCH(TRUE(),
        __Age>=25 && __Age<30,"25-30",
        __Age>=30 && __Age<50,"30-50",
        __Age>=50 && __Age<70,"50-70"
    )