topic Re: Calculating age at the point in time in DAX Commands and Tips

Calculating age at the point in time

Aleks13311 — Tue, 20 Aug 2024 11:03:48 GMT

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

Re: Calculating age at the point in time

lbendlin — Wed, 21 Aug 2024 00:41:57 GMT

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

Re: Calculating age at the point in time

Aleks13311 — Wed, 21 Aug 2024 08:35:01 GMT

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. 🙂

Re: Calculating age at the point in time

Anonymous — Tue, 27 Aug 2024 02:05:25 GMT

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.

Re: Calculating age at the point in time

Aleks13311 — Thu, 29 Aug 2024 13:32:57 GMT

Thank you so much, it worked!