cancel
Showing results for
Did you mean:
Highlighted
Member

## Pass slicer value to calculate date value

Hi folks,

I have read existing threads on this forum but still can't figure out a way to solve my problem, which isn't even the most complex thing I've done!

I have:

• a dataset which contains employee name, date of birth
• another manually created table (Expected Retirement Age) with a column (Age) containing numbers from 60 to 80

I would like to create for each record of the first table, a value with an expected year of retirement based on a selection in a slicer. While the formulas work, they are not returning what I expect. I either end-up with a very large number or the same number as today().

I assume it has to be with a measure rather than a column so the data refreshes as I change the selection but I can't figure it out. Any suggestions?

OF

2 ACCEPTED SOLUTIONS

Accepted Solutions
Member

## Re: Pass slicer value to calculate date value

i think i understand your issue

give this a try

measure =
--first get the selected value from the age colunm
var years = SELECTEDVALUE(Table2[Age])
-- get the max Dateofbirth max is needed for the measure incase there are two entries for the same name
var dob = max(Table1[dob])
-- brake the maxdob down into the parts of the date
var d = day(dob)
var m = month(dob)
var y = year(dob) + years

--put the date back together and return it
return DATE(y,m,d)

Member

## Re: Pass slicer value to calculate date value

i would also say that it is best to use a What if Paramater instead of a table of values for the age

i have also created a file that uses a what if paramater for the age and then uses this to calcualte the measure

What If PBIX FILE

Below is a link for how to create a what if paramater

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

8 REPLIES 8
Community Support Team

## Re: Pass slicer value to calculate date value

Hi @OzzieFrog ,

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Pass slicer value to calculate date value

i think i understand your issue

give this a try

measure =
--first get the selected value from the age colunm
var years = SELECTEDVALUE(Table2[Age])
-- get the max Dateofbirth max is needed for the measure incase there are two entries for the same name
var dob = max(Table1[dob])
-- brake the maxdob down into the parts of the date
var d = day(dob)
var m = month(dob)
var y = year(dob) + years

--put the date back together and return it
return DATE(y,m,d)

Member

## Re: Pass slicer value to calculate date value

@AnthonyTilley, what you described is exactly what I am trying to do

I've tried just about every combination possible but I can't get it to work, getting an error message on the last formula Date(y,m,d) saying that An Argument of function DATE has the wrong data type or the result is too large or too small.

Could you please confirm that all the formulas you provided are supposed to be measures rather than columns?

I fail to see what else can go wrong, your solution seems ideal! I can't refresh my powerbi file atm (missing the ACE.OLEDB connector so need IT helpdesk to fix it) but will try to upload it unless you have a suggestion

OF

Member

## Re: Pass slicer value to calculate date value

The formula i provided should be used as one Mesaure not as individual measures.

simply copy the whole lot into one new measure and you should get the result your looking for.

also check that your Age colunm is formated as a number and not as a string otherwise the adding it to years will produce a sting of (example 201864) which is to big to be a 4 digit year

Member

## Re: Pass slicer value to calculate date value

here is a template file that uses the measure along with a name, date of birth table and and age table

PBIX FILE

if your still stuggling then try and send me you PBIX file and i will asssit

Member

## Re: Pass slicer value to calculate date value

i would also say that it is best to use a What if Paramater instead of a table of values for the age

i have also created a file that uses a what if paramater for the age and then uses this to calcualte the measure

What If PBIX FILE

Below is a link for how to create a what if paramater

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Member

## Re: Pass slicer value to calculate date value

@AnthonyTilley , thanks again for the clarification.

I've followed your steps (and learnt about parameters while I was there) but the moment I add the parameter as a slicer, the measure can't return a number with the same error message as above. I have checked that my parameter is formated as a number but I can't get it. Hopefully I can refresh the model tomorrow and attach it to this thread with sanitized data.

As a side note, is it normal that I can't use this measure (currently not adjusted by the retirement age parameter) in a diagram? I'm trying to show how many employees will retire by year for the next x years?

Member

## Re: Pass slicer value to calculate date value

@AnthonyTilley , your solution worked so thank you very much

I managed to refresh my excel file and remove blanks in dob which were I believe creating the issue, so you're a legend!

One final question, do you believe there is a way for me to use this year measure as an x-axis in a bar graph, so I can visualize a count of employees retiring by year?