cancel
Showing results 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

Frequent Visitor

## Calculate dynamic change of age and age grouping

Hello everyone,

Its seems simple but I dont have a clue how to get it work.

I have a table with ID, Name, Birth Date, Age, Start Date, End Date data, and additional table like Age (numbers from 0-100), and Date (calendar).

What I need to get is the ages of only actual employees on a given month of given year.

For example in 2012 I have only one employee and I want to see only one employee in a table.

Plus I want a graph showing age range (>30, 30-40, 40+)

I got separate measures for this tasks but I cannot combine them to work not separatly but in connection.

Measure for age calculation:

```Count By Age =
IF (
ISFILTERED ( Age[Age] ),
VAR MaxDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
FILTER (
VAR AgeCalculation =
IF (
TRUNC ( YEARFRAC ( HeadReport[Birth Date], MaxDate ) )
)
RETURN
CONTAINS ( VALUES ( Age[Age] ), Age[Age], AgeCalculation )
)
),
)```

Measure for actual employees calculation:

```Actual Employees =
VAR MaxDate = MAX ( 'Date'[Date] )

VAR EmpCnt =
CALCULATE (
COUNTROWS (

),
)
RETURN
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt)```

Measure for Age range:

```Age range = IF(HeadReport[Count By Age]>40,"40+",
"Less 30"))```

Any help would be greatly appreciated.

Example pbix:

https://www.dropbox.com/s/8k1triyxisq9a93/Age%20calculation.pbix?dl=0

1 ACCEPTED SOLUTION
Super User

Hi @Artm ,

First of all I believe that for your calculations you don't need the additional columns and table for age, you can setup a table with the date ranges and add two measure to calculate the two fields you need.

 ID Group Min Max 1 <30 0 29 2 30-40 30 40 3 40+ 41 999

• Create the following two measures:
```Number Employess by date =
COUNTROWS (
FILTER (
(HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] == BLANK ())  ||(HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] >= MAX ( 'Date'[Date] ))
)
)```
```Age range Calculation =
CALCULATE (
COUNTROWS (
FILTER (
SUMMARIZE (
ALL (
);
"@Age"; DATEDIFF ( HeadReport[Birth Date]; MAX ( 'Date'[Date] ); YEAR )
);
[@Age] <= max ( 'Age Periods'[Max] )
&& [@Age] >= MAX ( 'Age Periods'[Min] )
&& (
(
HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] == BLANK ()
)
|| (
HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] >= MAX ( 'Date'[Date] )
)
)
)
)
)```

Check the result below (top table is your calculations down is using the two measure as you can see result is the same.

See PBIX file attach (without all the additional columns/table that are not needed for the calculation) compared it with your file and please tell me if ti works).

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

4 REPLIES 4
Anonymous
Not applicable
Frequent Visitor

Thanks maguilarBI, but its not actually what I expected.

I updated a little bit my data (add a range column to Age table) and got the following:

As you can see everything almost works but it shows me all employees or all ranges.

I cant find out the proper filter data to sort it.

Updated pbix:

https://www.dropbox.com/s/8k1triyxisq9a93/Age%20calculation.pbix?dl=0

Super User

Hi @Artm ,

First of all I believe that for your calculations you don't need the additional columns and table for age, you can setup a table with the date ranges and add two measure to calculate the two fields you need.

 ID Group Min Max 1 <30 0 29 2 30-40 30 40 3 40+ 41 999

• Create the following two measures:
```Number Employess by date =
COUNTROWS (
FILTER (
(HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] == BLANK ())  ||(HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] >= MAX ( 'Date'[Date] ))
)
)```
```Age range Calculation =
CALCULATE (
COUNTROWS (
FILTER (
SUMMARIZE (
ALL (
);
"@Age"; DATEDIFF ( HeadReport[Birth Date]; MAX ( 'Date'[Date] ); YEAR )
);
[@Age] <= max ( 'Age Periods'[Max] )
&& [@Age] >= MAX ( 'Age Periods'[Min] )
&& (
(
HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] == BLANK ()
)
|| (
HeadReport[Start Date] <= max ( 'Date'[Date] )
&& HeadReport[End Date] >= MAX ( 'Date'[Date] )
)
)
)
)
)```

Check the result below (top table is your calculations down is using the two measure as you can see result is the same.

See PBIX file attach (without all the additional columns/table that are not needed for the calculation) compared it with your file and please tell me if ti works).

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Hi MFelix

Yes, thats what I actually need. Thanks for your help and explanation. Really appreciate it.

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.