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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate staff pool by age

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

1 ACCEPTED SOLUTION
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"
)

@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
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

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

@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors