topic Re: Use selected date slicer value to calculate ages in a column in DAX Commands and Tips

Use selected date slicer value to calculate ages in a column

ses6791 — Sun, 08 Sep 2024 13:23:20 GMT

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

Re: Use selected date slicer value to calculate ages in a column

FlipFlop1 — Sun, 08 Sep 2024 14:12:01 GMT

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

Re: Use selected date slicer value to calculate ages in a column

ses6791 — Sun, 08 Sep 2024 19:55:53 GMT

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

Re: Use selected date slicer value to calculate ages in a column

Selva-Salimi — Mon, 09 Sep 2024 09:30:54 GMT

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.

Re: Use selected date slicer value to calculate ages in a column

Anonymous — Tue, 10 Sep 2024 02:20:25 GMT

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.

Re: Use selected date slicer value to calculate ages in a column

ses6791 — Tue, 10 Sep 2024 22:23:17 GMT

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)

Re: Use selected date slicer value to calculate ages in a column

Anonymous — Fri, 20 Sep 2024 07:51:42 GMT

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.