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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
SamBowdell
New Member

Retirement calculations

I have a table with employee data with the following information-

Employee number, name, fte, dob, age, date 

 

There is a row for every month they are employed with us.

I need to show a bar chart with a count by of staff sliced by age.

 

The outcome will mean I am able to see the count drop when people reach a set age 

Example

If 25 staff hit 65 in Sept 2023 then the total number for sept will be 65 less.

 

I also have a date table.

 

Thank you

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @SamBowdell ,

 

I suggest you to create a data model as below.

vrzhoumsft_0-1689837245140.png

Fact table:

vrzhoumsft_2-1689837314837.png

Measure:

Count by Age = 
VAR _SELECTAGE =
    SELECTEDVALUE ( DimAge[Age] )
VAR _STEP1 =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Current Age", QUOTIENT ( DATEDIFF ( 'Table'[Dob], MAX ( DimDate[Date] ), MONTH ), 12 )
    )
RETURN
    COUNTAX ( FILTER ( _STEP1, [Current Age] < _SELECTAGE ), [Name] )

Result is as below.

vrzhoumsft_1-1689837272126.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

Hi @SamBowdell ,

 

I suggest you to create a data model as below.

vrzhoumsft_0-1689837245140.png

Fact table:

vrzhoumsft_2-1689837314837.png

Measure:

Count by Age = 
VAR _SELECTAGE =
    SELECTEDVALUE ( DimAge[Age] )
VAR _STEP1 =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Current Age", QUOTIENT ( DATEDIFF ( 'Table'[Dob], MAX ( DimDate[Date] ), MONTH ), 12 )
    )
RETURN
    COUNTAX ( FILTER ( _STEP1, [Current Age] < _SELECTAGE ), [Name] )

Result is as below.

vrzhoumsft_1-1689837272126.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rubayatyasmin
Super User
Super User

Hi, @SamBowdell 

 

1. Create  a calculated column using.

 

Retired = IF('EmployeeTable'[Age] >= 65, "Yes", "No")

 

2. Create measure for active employee count. 

 

ActiveCount = CALCULATE(
COUNT('EmployeeTable'[Employee number]),
FILTER('EmployeeTable', 'EmployeeTable'[Retired] = "No")
)

 

Hope this helps. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


I want to be able to select any age

try using SELECTEDVALUE DAX. here is the documentation

 

https://learn.microsoft.com/en-us/dax/selectedvalue-function

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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