cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LQQ Frequent Visitor
Frequent Visitor

Need Help: using parameter in calculated column

I have staff birthday as such

StaffBirthday
E21/2/1977
D3/11/1978
G26/9/1979
B1/2/1980
C8/5/1985
A15/8/1985
F15/10/1985

 

I want to have a parameter which has values 62,65,67 to represent the age when staff retire. for each value, i need to count how many staff still in service over time. for example, if parameter value is 62, then i can calculate the retirement year using birthday + 62*365 to get retirement year.

then plot line chart using retirement year as x-axis and y -axis to be count number of staff still in service (not retired) so as to show the staff reduction over time. The line chart need to change if user choose a different retire year (65 or 67)

StaffBirthdayif retire at 62count in serviceif retire at 65if retire at 67
E21/2/19776/2/203965/2/20425/2/2044
D3/11/197818/10/2040518/10/204317/10/2045
G26/9/197910/9/204149/9/20449/9/2046
B1/2/198016/1/2042315/1/204515/1/2047
C8/5/198523/4/2047022/4/205021/4/2052
A15/8/198531/7/2047030/7/205029/7/2052
F15/10/198530/9/2047029/9/205028/9/2052

 

The chart i want to plot is same as the Exel version : https://drive.google.com/file/d/1MNjl4IWKOpqLAOZWwd7zw3PkiFljBbXx/view?usp=sharing 

I am not sure how to do this effectively. can anyone help? Thanks a million!

6 REPLIES 6
Super User
Super User

Re: Need Help: using parameter in calculated column

Hi,

 

I am not sure of what chart you want to create but here is your expected output in a Tabular format.  You may download my PBI file from here.

 

Untitled.png

LQQ Frequent Visitor
Frequent Visitor

Re: Need Help: using parameter in calculated column

Thanks for your reply. 

but after getting retirement year, how to count number of staff who is not retired from current 2019 to 2052 and plot the count in line chart over years? Thanks in advance

Super User
Super User

Re: Need Help: using parameter in calculated column

I am not clear about your question.  Based on data that you have shared, show the expected result in a simple Table.

LQQ Frequent Visitor
Frequent Visitor

Re: Need Help: using parameter in calculated column

for example, the x-axis show year 2019, 2020, 2021... 2052, the y axis shows count of staff who are not retired, so for 2019, count is 7 (total staff), for 2039 count is 6 (cos 1 staff retire 7-1=6), for 2040, count is 5 (2 staff retired 7-2=5), etc.

LQQ Frequent Visitor
Frequent Visitor

Re: Need Help: using parameter in calculated column

the chart i want to plot is same as this Excel version : https://drive.google.com/file/d/1MNjl4IWKOpqLAOZWwd7zw3PkiFljBbXx/view?usp=sharing 

Super User
Super User

Re: Need Help: using parameter in calculated column

Hi,

 

Can you live with this.  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png