cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
OzzieFrog Member
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?

 

Thanks in advance

OF

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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)
-- for the year add on your selected value
var y = year(dob) + years
 
--put the date back together and return it 
return DATE(y,m,d)


retirment age.png

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
Community Support Team

Re: Pass slicer value to calculate date value

Hi @OzzieFrog ,

Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

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.

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)
-- for the year add on your selected value
var y = year(dob) + years
 
--put the date back together and return it 
return DATE(y,m,d)


retirment age.png
OzzieFrog Member
Member

Re: Pass slicer value to calculate date value

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

 

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 Smiley Happy

 

Thanks again for your help.

OF

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 

 

 

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

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

OzzieFrog Member
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?

OzzieFrog Member
Member

Re: Pass slicer value to calculate date value

@AnthonyTilley , your solution worked so thank you very much Smiley Happy

 

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?