Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
AnthonyTilley
Solution Sage
Solution Sage

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
AnthonyTilley
Solution Sage
Solution Sage

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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 🙂

 

Thanks again for your help.

OF

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 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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?

Anonymous
Not applicable

@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?

v-danhe-msft
Employee
Employee

Hi @Anonymous ,

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.