Use selected date slicer value to calculate ages in a column

[ses6791]

Hi, I hope someone can help. I have seen other similar posts but i don't think they're answering my query. I am trying to use a date value selected by the user in a slicer to calculate the ages of the individual records in my source table. So my column formula would be like "(Selected slicer date - record Date of Birth)/365". The slicer dates represent the beginning of a period (i.e. 1st Jan 2022, 1st Jan 2023, 1st Jan 2024 etc) and i want to calculate how old the individual records were at these points in time. Eventually, i will have a further dropdown slicer where the user can select an age group (where age is calculated at the selected period beginning) to show in a visual. Is this possible? Thanks

[v-kaiyue-msft]

Hi @ses6791 ,

Based on your description, I created these data.

1. enter a new table here with the start of a period written in it to be used as a slicer field.

2. create measure, which calculates the age.

Measure = 
(SELECTEDVALUE('Table 2'[beginning of a period]) - MAX('Table'[DOB])) / 365

3. enter a table again with age groups written in it to use as a slicer.

4. Create a measure to calculate which age group the current client belongs to.

Measure 2 = 
SWITCH(TRUE(),
'Table'[Measure] < 18, "under 18",
'Table'[Measure] >= 18 && 'Table'[Measure] <60, "18-59",
'Table'[Measure] >= 60,"60+")

Measure 3 = 
IF([Measure 2] = SELECTEDVALUE('Table 3'[age group]),1)

5. Drag and drop measure 3 into the filter window of the table visual object to filter the data with value 1.

The final result is shown below, more detailed information can refer to the attachment.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ses6791]

Hi, that was a really great reply, thanks. I think my post heading was a bit misleading as what i want to be able to do is create a visual a bit like (1) below. So rather than a visual that lists all the clients and their DOBs and age at beginning of the period, i would like to be able to count the clients that are in the chosen age group. So in my basic example (1) below, i have chosen Gender as a column and i then put in a count column so that it gives a count of the number of Males and Females. I want to be able to do the same with the choose the "beginning of period" and the "age group" that you created, but when i do it with Measure 3 in the filter window, it doesn't show any values or Total at all (see (2)). I have saved what i've done in the pbix but i don't seem to be able to attach it. Very grateful if you could tell me where i'm going wrong. Thanks

(1)

(2)

[v-kaiyue-msft · Accepted solution]

Hi @ses6791 ,

You can create another measure on top of the previous one.

Measure 4 = 
COUNTX(FILTER('Table','Table'[Measure 3] = 1),'Table'[Measure])

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[FlipFlop1]

Given a Customer table with a field DOB and a Date Table in which you use the DateKey as a page filter.

To understand what's going on I recommend creating a separate measure for  SELECTEDVALUE(DimDate[Datekey]) and then dropping that into a card visual so you can see how this changes as you select the date filter. Here is the DAX for a measure 'Age' that will do the work for you.

Age =
      VAR DOB = MIN(Customers[DOB])

      VAR Selected_Date = SELECTEDVALUE(DimDate[Datekey])

      VAR Age =  ([DOB]-[Selected Date])/365

      RETURN Age

[ses6791]

Hi, thanks very much for your response. I think i've got it all working up to the point where the Age column doesn't seem to change when i choose different dates. I'll send you some screenshots of what i've set up. Any ideas gratefully received. Thanks

[Selva-Salimi]

Hi @ses6791 ,

yes, it is possible to calculate the age based on the starting date of the selected period. the solution is to write a measure insted of column as follows:

Measure_Age := (MIN(Customers[DOB])- MIN(DimDate[Datekey])) / 365

If this post helps, then I would appreciate a thumbs up 👍  and mark it as the solution to help the other members find it more quickly.