Calculate dynamic change of age and age grouping

[Artm]

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 (
            COUNTROWS ( HeadReport ),
            FILTER (
                VALUES ( HeadReport[Birth Date] ),
                VAR AgeCalculation =
                    IF (
                        HeadReport[Birth Date] <= MaxDate,
                        TRUNC ( YEARFRAC ( HeadReport[Birth Date], MaxDate ) )
                    )
                RETURN
                    CONTAINS ( VALUES ( Age[Age] ), Age[Age], AgeCalculation )
            )
        ),
    COUNTROWS ( HeadReport )
)

Measure for actual employees calculation:

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

VAR EmpCnt =
    CALCULATE (
        COUNTROWS (
            CALCULATETABLE ( Headreport, HeadReport[Start Date]<= MaxDate, ALL ( 'Date' ) )

                                ),
                              (ISBLANK ( HeadReport[End Date]) || HeadReport[End Date] > MaxDate)
                       )
RETURN
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt)

Measure for Age range:

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

Any help would be greatly appreciated.

Example pbix:

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

[Anonymous]

i hope this can help you:

https://drive.google.com/open?id=1iUtu1AMuWtOufdi1HyUaZBsyMfqWVVRi

[Artm]

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

[MFelix · Accepted solution]

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.

• Add the following disconnected table to your model:

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 (
        ALL ( HeadReport[ID]; HeadReport[Start Date]; HeadReport[End Date] );
        (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 (
                    HeadReport[ID];
                    HeadReport[Start Date];
                    HeadReport[End Date];
                    HeadReport[Birth Date]
                );
                HeadReport[ID];
                HeadReport[Birth Date];
                HeadReport[Start Date];
                HeadReport[End Date];
                "@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

[Artm]

Hi MFelix, 

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