Calculating age at the point in time

[Aleks13311]

Hi, our data is directly connected to PowerBI, and I don't have access to Power Query. I want to create a line graph showing the trends over time for the number of active customers during a specific period, and how many of those customers were over 65 years old. I have a graph set up as shown below, and the relevant columns in my People Table are: [PERSON_ID], [DOB], [COMMITMENT_START_DATE], and [COMMITMENT_END_DATE].

I'm using DAX to calculate the number of people: 

Total Adults =

VAR maxDate = EOMONTH(MAX('Dates Table '[Date]), 0)  // 0 represents the current month

RETURN

    CALCULATE(

        DISTINCTCOUNT('People Table '[PERSON_ID]),

        ALLSELECTED('Dates Table '[Date]),

        'Dates Table'[Date] <= maxDate,

        ISBLANK('People Table '[COMMITMENT_END_DATE])

        || ''People Table'[COMMITMENT_END_DATE] >= maxDate

    )

I need a DAX formula to calculate the number of people under 65 to include in the graph. I've experimented with various formulas to get the correct count of people under 65, but while it works for the most recent month, it produces incorrect results for dates further back, such as a year ago. One of the DAX formulas I tried is (but it returns inaccurate results):

Adults Under 65 =

VAR maxDate = EOMONTH(MAX('Dates Table '[Date]), 0)  // 0 represents the current month

VAR Under65 =

    CALCULATE(

        DISTINCTCOUNT(People Table [PERSON_ID]),

        ALLSELECTED('Dates Table '[Date]), 

        'Dates Table'[Date] <= maxDate,

        ISBLANK(People Table [COMMITMENT_END_DATE])

        || People Table [COMMITMENT_END_DATE] >= maxDate,

        INT(YEARFRAC(People Table [DOB], maxDate, 1)) < 65

    )

RETURN

Under65

How Can I do it please? Thanks Aleks

[Anonymous · Accepted solution]

Hi @Aleks13311 ,

Depend on your descriptions, try to modify your formula like below:

AdultsUnder65 = 
VAR maxDate = EOMONTH(MAX('Dates Table'[Date]), 0)  // 0 represents the current month
RETURN
    CALCULATE(
        DISTINCTCOUNT('People Table'[PERSON_ID]),
        ALLSELECTED('Dates Table'[Date]),
        'Dates Table'[Date] <= maxDate,
        ISBLANK('People Table'[COMMITMENT_END_DATE]) || 'People Table'[COMMITMENT_END_DATE] >= maxDate,
        DATEDIFF('People Table'[DOB], maxDate, YEAR) < 65
    )

Best Regards,
Adamk Kong

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

[Aleks13311]

Thank you so much, it worked!

[lbendlin]

Are you live connecting to someone else's semantic model?

[Aleks13311]

Thank you for your response. I'm dealing with a tricky situation. We are currently live-connected to our database, where we record client information, and Power BI is directly connected to this database. Previously, we used an intermediate system to export the data, but that system has been dismantled. As a result, our database engineers opted to connect Power BI directly to the database.

The challenge I'm facing is with calculating the age of customers. When I try to calculate a customer's age, it always reflects their age as of today, since Power BI is pulling real-time data. I haven't been able to find a solution that would allow me to determine how old a person was at a specific point in the past. I can calculate their age on a specific date, but I need a way to calculate their age at any point in time historically.

I'm not sure if I'm explaining this clearly or if what I'm asking for is even possible. Any advice would be greatly appreciated. 🙂