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

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
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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.