Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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().
Thanks in advance
OF
Solved! Go to Solution.
i think i understand your issue
give this a try
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
Below is a link for how to create a what if paramater
https://docs.microsoft.com/en-us/power-bi/desktop-what-if
Proud to be a Super User!
i think i understand your issue
give this a try
Proud to be a Super User!
@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
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
if your still stuggling then try and send me you PBIX file and i will asssit
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
Below is a link for how to create a what if paramater
https://docs.microsoft.com/en-us/power-bi/desktop-what-if
Proud to be a Super User!
@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?
@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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |