Dynamic Age Calculation Based on User Selected Cutoff Date

[tachydidaxy]

I have a customer table that includes a date of birth field; our customer table also includes children of customers.

My goal is to create a visual that allows one to select an arbitrary date in the future which based on our current customer table returns how many children will be between the ages of five (5) and ten (10) on the date selected.

I was hoping for a nudge in the right direction conceptually here as I've run into a problem trying to group the children by the age they'd be on the selected cutoff date.

Here's what I currently have:

I have a disconnected table called "cutoff date" that allows one to select desired date for the cutoff (this will be dropped into the visual as a filter object to allow the user to select the desired date).

I also have a measure that harvests the selected value for the cutoff_date table:

Selected Cutoff = MAX ( 'Cutoff'[cutoff_date] )

I know that I can use YEARFRAC to calculate the age, so here's the measure I've created to return the total count of children aged five to ten based on the selected cutoff year:

Children Aged Five to Ten Based on Selected Cutoff Date =
CALCULATE (
    COUNTROWS ( 'Member' ),
    FILTER (
        'Member',
        NOT ( ISBLANK ( 'Member'[date_of_birth] ) ) && 'Member'[is_active_dep]
            && (
                TRUNC ( YEARFRAC ( 'Member'[date_of_birth], [Selected Cutoff] ) ) >= 5
                    && TRUNC ( YEARFRAC ( 'Member'[date_of_birth], [Selected Cutoff] ) ) <= 10
            )
    )
)

This measure works well and responds as expected to the selected date from the cutoff filter.

My question is I also want to be able to produce a chart that shows the age of the child based on the cutoff date selected, not the age of the child currently. I need to group them by their age based on the cutoff date.

I'm stumped because I understand that I'd need to use a column in order to be able to drop the value in either the Legend or Details fields (won't accept measures). I just don't understand how I'd create the calculated column that calculates their age based on the slicer selection.

To be clear, below is the desired visual, which right now is only showing their current age (based on date the report is run) and is NOT showing the age based on the cutoff_date slicer selection.

Here's my first attempt at creating the calculated column which does not work. I think the problem is with my "harvester measure" [Selected Cutoff], that measure appears to always returns 6/30/2022 as the max date for my calculated column even if a different selection has been made on the report.

age_at_date_selected =
IF (
    NOT ( ISBLANK ( 'Member'[date_of_birth] ) ),
    TRUNC ( YEARFRAC ( 'Member'[date_of_birth], [Selected Cutoff] ) )
)

Any insight and assistance would be greatly appreciated.

[v-yulgu-msft]

Hi @tachydidaxy,

---

I'm stumped because I understand that I'd need to use a column in order to be able to drop the value in either the Legend or Details fields (won't accept measures). I just don't understand how I'd create the calculated column that calculates their age based on the slicer selection.

To be clear, below is the desired visual, which right now is only showing their current age (based on date the report is run) and is NOT showing the age based on the cutoff_date slicer selection.

---

Please be aware of that calculated column returns static values since it is initialized. And it won't be dynamically changed based on slicer selection.

Regards,

Yuliana Gu

[tachydidaxy]

Still struggling with this; in a nutshell what I think I need is a way to create a calculated column in a table that will respond to a selected filter context from the report page. 

I've tried numerous ways (admitedly blindly) and can't seem to get there (none of the tries below worked):

age_at_date_selected_trial1 =
CALCULATE (
    IF (
        NOT ( ISBLANK ( MAX ( 'Member'[date_of_birth] ) ) ),
        TRUNC (
            YEARFRAC (
                MAX ( 'Member'[date_of_birth] ),
                SELECTEDVALUE ( 'Cutoff'[cutoff_date] )
            )
        )
    )
)

age_at_date_selected_trial2 =
CALCULATE (
    IF (
        NOT ( ISBLANK ( MAX ( 'Member'[date_of_birth] ) ) )
            && HASONEVALUE ( 'Cutoff'[cutoff_date] ),
        TRUNC (
            YEARFRAC (
                MAX ( 'Member'[date_of_birth] ),
                SELECTEDVALUE ( 'Cutoff'[cutoff_date] )
            )
        )
    )
)

age_at_date_selected_trial3 =
CALCULATE (
    IF (
        NOT ( ISBLANK ( MAX ( 'Member'[date_of_birth] ) ) )
            && HASONEVALUE ( 'Cutoff'[cutoff_date] ),
        TRUNC (
            YEARFRAC ( MAX ( 'Member'[date_of_birth] ), VALUES ( 'Cutoff'[cutoff_date] ) )
        )
    )
)

If anyone can nudge me in the right direction I'd be really grateful.

[OwenAuger · Accepted solution]

Hi @tachydidaxy

Unfortunately as Yuliana mentioned, calculated columns don't respond to any filters within the report. Calculated columns are populated at report refresh, in an 'unfiltered' filter context.

For what you're trying to do, you would need to use something like a Dynamic Segmentation pattern (see DAX Patterns page).

1. Create an 'Age' table containing all possible Ages you might want to filter on.
2. Include the Age[Age] column in your visual.
3. Create a Member Count By Age measure following a Dynamic Segmentation type pattern:
   

   Member Count By Age =
   IF (
       ISFILTERED ( Age[Age] ),
       VAR SelectedCutoff =
           MAX ( Cutoff[cutoff_date] )
       RETURN
           CALCULATE (
               COUNTROWS ( 'Member' ),
               FILTER (
                   VALUES ( 'Member'[date_of_birth] ),
                   VAR AgeCalculated =
                       IF (
                           'Member'[date_of_birth] <= SelectedCutoff,
                           TRUNC ( YEARFRAC ( 'Member'[date_of_birth], SelectedCutoff ) )
                       )
                   RETURN
                       CONTAINS ( VALUES ( Age[Age] ), Age[Age], AgeCalculated )
               )
           ),
       COUNTROWS ( 'Member' )
   )

   (I modified the pattern slightly)

Note that this measure will return a simple count of Members if no Ages are filtered on.

Also, ages are only computed if date_of_birth <= Selected cutoff_date.

Here is a sample pbix demonstrating this.

https://www.dropbox.com/s/5p1faipgycwz5zo/Age%20Dynamic%20Segmentation.pbix?dl=1

Regards,

Owen

[tachydidaxy]

@OwenAuger

Wow, that's amazing, can't thank you enough for the insight and assistance. I'm working to digest the Dax patterns link you included as best I can. The CONTAINS() function was new to me and is obviously a key component of this measure. 

I did have to edit just a little to adjust (see below), but there's no way this would have happened without your invaluable input. Thanks again!

Dependent Count by Age Based on Selected Cutoff = 
IF (
    ISFILTERED ( Age[Age] ),
    VAR SelectedCutoff =
        MAX ( Cutoff[cutoff_date] )
    RETURN
        CALCULATE (
            COUNTROWS ( 'Member' ),
            FILTER ( 'Member', 'Member'[is_active_dep] ),
            FILTER (
                VALUES ( 'Member'[date_of_birth] ),
                VAR AgeCalculated =
                    IF (
                        NOT ( ISBLANK ( 'Member'[date_of_birth] ) )
                            && 'Member'[date_of_birth] <= SelectedCutoff,
                        TRUNC ( YEARFRAC ( 'Member'[date_of_birth], SelectedCutoff ) )
                    )
                RETURN
                    CONTAINS ( VALUES ( Age[Age] ), Age[Age], AgeCalculated )
            )
        ),
    COUNTROWS ( 'Member' )
)

@v-yulgu-msft

Thank you for response and assistance, I really do appreciate it.