Power BI Dynamic Calculation based on dynamic filter and date selection

Question

I have the following scenario-

1. Age category (Adult, Child and You Adult)- It is coming from a table. The column is being used as a slicer:

Adult - age 26 and above (>26)

Young Adult - age 18 to 26 (>=18 and <=26)

Child - age less than 18 (<18)

2. I have a table with unique ID's and date of birth
3. A third table (as a date dimension), which gives me sample dates that the user can select from a slicer.

Requirement -

1. The user will select a date from the Day slicer and a category from the category slicer.

2. Based on the day selection, the age calculation has to be dynamic and based on the category, the count of rows from the second table are to be returned.

For example - I select 06/06/2022 - based on this date, the age of the rows of the second table will be calculated, and I select category "Young Adult". Hence, the count will only be of rows which have age from 18 to 26.

I have tried to make it work but unsuccessfully. I would appreciate if someone could guide me to a solution if at all this is possible to do in PBI. Thank you.

Link to my PBI file - https://github.com/sommkh/pbi_age_test/blob/2cc9eb7dbe7788ea21da5ba1bb8c7f08adca60e2/Age_Test_Dataset.pbix

Answer 1

First you should put the minimum and maximum age for the categories into the AGE_CATEGORY table. The table then looks like this:

The measures can then be simplified to

AGE_IN_YRS = 
VAR date_selected = SELECTEDVALUE(D_DATE[DATE])
VAR birthday = SELECTEDVALUE(AGE_TEST_DATA[IND_BIRTH_DT])
VAR age_in_yrs = FLOOR( IF ( birthday < date_selected, DATEDIFF ( birthday, 
     date_selected, DAY ) / 365.25, -1), 1)
RETURN age_in_yrs

and

AGE_AGGRE = 
VAR minage = SELECTEDVALUE(AGE_CATEGORY[MinAge])
VAR maxage = SELECTEDVALUE(AGE_CATEGORY[MaxAge])
RETURN 
SUMX(AGE_TEST_DATA,
     VAR age = [AGE_IN_YRS] 
     RETURN if (age <> -1 && age >=minage && age <= maxage,1,0)
     )
--People that are not yet born (-1) are not counted

The result: