Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
fep
Helper I
Helper I

Calculate staff pool by age

Hi,

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

for example:

fep_0-1599411235246.png

 

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.

 

fep_1-1599411385124.png

 

Thanks

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@fep - 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"
    )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@fep , 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-bi...
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...

 

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Super User
Super User

@fep - 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"
    )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.