How to used selected slicer date to create a measure

[hpatel24779]

Hi All,

I want to know if it is possible to use the selected date in a slicer to create a measure.

i have a calendar table and persons table. i want to calculate the age of every person based on the quarter and financial year selected in the slicer. in my calendar table, i have calculated the quarter end date so if Q2 of financial year 2024-25 is selected, this date would be 30/09/2024.

So i want to use this selected date to calculate the age.

i have tried selectedvalue(quarterendate)-date of birth but this returns blank values.

Can anyone help as i have been trying for ages but no luck.

kind regards

Hetal

[SVADDE21 · Accepted solution]

Hello @hpatel24779 ,

Yes, you will be able to calculate the value based on slected clicer value. Please ensure you have relation between Calendar table and Persons table.

Please check if this works.

SelectedQuarterEndDate = SELECTEDVALUE(Calendar[QuarterEndDate]) //This is to capture the value of the slicer you selected.//

AgeAtSelectedDate = 
DATEDIFF(Persons[Birthdate], [SelectedQuarterEndDate], YEAR)

[v-tsaipranay]

Hi @hpatel24779 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @hpatel24779 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Ashish_Mathur]

Hi,

Assuming your want a measure output and in yout table visual, you have dragged employee name from the Data Table, write this measure

Measure = datediff(min(Data[DoB]),min(calendar[date]),year)

Create a slicer from the Calendar table and select a year/quarter there.

[v-tsaipranay · Accepted solution]

Hi @hpatel24779 ,

Thank you for reaching out to the Microsoft Fabric Community.

@SVADDE21  solution is correct, but here are a few things to check:

Ensure there's a valid relationship between your Calendar and Persons tables. Without this, SELECTEDVALUE() won't work as expected and confirm that a valid date is selected in the slicer.

The DATEDIFF() function should correctly calculate the age:

SelectedQuarterEndDate = SELECTEDVALUE(Calendar[QuarterEndDate])
AgeAtSelectedDate = DATEDIFF(Persons[Birthdate], [SelectedQuarterEndDate], YEAR)

If you're still seeing blank values, please verify the relationship and slicer selection.

I hope this will reslove your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

[v-tsaipranay]

Hi @hpatel24779 ,

I wanted to check if you had the opportunity to review the information provided. Also thankyou @Ashish_Mathur for your inputs, Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[SVADDE21 · Accepted solution]

Hello @hpatel24779 ,

Yes, you will be able to calculate the value based on slected clicer value. Please ensure you have relation between Calendar table and Persons table.

Please check if this works.

SelectedQuarterEndDate = SELECTEDVALUE(Calendar[QuarterEndDate]) //This is to capture the value of the slicer you selected.//

AgeAtSelectedDate = 
DATEDIFF(Persons[Birthdate], [SelectedQuarterEndDate], YEAR)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...