Employee turnover is a term that is widely used in HR analysis. Even though I am not an HR Professional, I know that people define Employee turnover differently.
And I’m going to go with the formula that is frequently found on the internet. (I’m pretty sure that if you learn this one, any other turnover formula will be easier to calculate using DAX 😉)
This formula seems pretty easy, right? Even so, calculating this in Power BI is a bit tricky.
Now roll up your sleeves because we are gonna do some serious DAX!😎
The only data we have with us to calculate the employee turnover are Employee ID/Name, Date of Appointment, and Date of Resignation (Whatever you call it in your organization)
To make everything easier, I went step by step breaking down the numerator and the denominator until the only pieces that left are the above data that I have.
I’ll explain using the DAX code itself.
The most important thing that you should have first is the Date Table. Believe me! It is a lifesaver! ❤️ You can simply use the CALENDARAUTO() function to create a Date Table. (There are some other functions too if you want to try them out.)
You’ll need the date table to refer to Employee start date(Date of Appointment) and Employee end date(Date of resignation).https://www.linkedin.com/embeds/publishingEmbed.html?articleId=7641001261280701347
So I am using an Employee Table which consists of Employee ID, Employee Name, Start Date, End Date, and other HR-related data.
Now let’s discuss how we can calculate turnover per month. You can tweak the code a bit to make it annual.
Make sure to add a column that calculates the Month and Year.
First things first, defining the variables! Put it simply, you need two main variables which you will be used to create other variables.
var MonthStartDate = STARTOFMONTH(‘Date'[Date])
var MonthEndDate = ENDOFMONTH(‘Date'[Date])
It’s time to get your hands dirty!
Now that you have Month Start Date and Month End Date in your hands, use the following code to calculate the number of employees during the month.
💡 You can use Start Date instead of End Date to calculate the number of employees joined during the month. (If you need it in another measure)
Here’s the tricky part!
You know that Average Employees in a period is calculated by dividing the sum of Employees as at the beginning of the month and Employees as at the end of the month by 2.
So I calculated these two separately.
Let’s talk about how we can calculate the number of employees at the beginning of the month. And you’ll be able to figure out the second by yourself.
Here’s what I did. I calculated ALL the employees joined until the beginning of the month. Then ALL the employees left until the beginning of the month. And got the difference!
Done? There you go!
You can do the same thing to calculate the number of employees as at the end of the month.
Now you have all the ingredients. You cut and chopped and mixed it well. Now go! Bake it in the oven. 🤪
Here’s the recipe again, in a nutshell!
TaDa!!!!!! Now you can use the Month and Year from the Date table and the Employee Turnover from the Employee table in a visualization!
Pretty dope right? 😌
Authored by Maneesha Wickramasinghe @ BISTEC Global
Recently one of our customers in Australia came to us to solve a problem yet they were facing in the support department. They still receive a lot of support requests daily. The solutions for most of these requests are already available in the user manuals, support site, and other materials to which the customers have access. Since many of these requests are easy to fix, it’s a struggle to invoice the customers properly. Also, a lot of support staff is needed to meet the demand. Our client wanted us to come up with a solution to this problem.
We looked at the following parameters to suggest a solution.
The channels of support request
The volume of resources (support documents)
Based on these facts we decided to organize the resources in a manner that a customer would be able to find out the answers using one of the above-mentioned mediums without having to contact the support staff.
We proposed an intelligent Chat-Bot that will be deployed on Microsoft Teams and in their Help Desk system. So, the customers would first chat with the bot and the bot would analyze the available resources for possible solutions. However if the Chat-Bot failed to respond positively then, it would create a support ticket and assign it to support staff.
We then looked at the tools that we can use to develop our Chat-Bot. Furthermore there are plenty of resources out there but, we felt the suite of tools provided by Microsoft are more reliable and easy to use. Microsoft has invested a lot in AI and Natural language processing and has come up with several tools that are ideal to build an intelligent Chat-Bot. Also, it’s easy to test and deploy these tools because of the integration with Azure.
QnA service extracts question and answer pairs from the content that you upload. The content can be uploaded as URLs, PDFs, Excel files, SharePoint documents, and many more.
Adaptive Card is an open-source toolset that helps apps and services exchange rich snippets of native UI.
Microsoft Teams is a unified communications platform that combines persistent workplace chat, video meetings, file storage, and application integration.
Azure Cosmos DB is Microsoft’s globally distributed, multi-model database service. With a click of a button, Cosmos DB enables you to elastically and independently scale throughput and storage across any number of Azure regions worldwide.
The ChatBot uses QnA service to query the proposed answers for customer queries. We have used Cosmos DB to store the chat session and related data. It’s deployed to Microsoft Teams as a plugin and is available on the support site as a widget.
Generally speaking, once a user enters the chat room in Microsoft Teams he would be initiating a chat with the Bot. The rest of the flow is as follows,
We first created the repository using all the documents, manuals, and URLs. The QnA service allows us to import these different types of resources and generates question and answer pairs. Also, we can extract multipart answers as well so that when a customer asks a question it will be able to refine the answers by giving options.
We first set up the knowledge base in the QnA maker. Go to https://www.qnamaker.ai/ and click on Create a knowledge base. You must go through 5 steps to set up the knowledge base.
Once the QnA maker is set up it gives us an interactive window to test the chat. Also, we can build a tool to train our Bot using the Train API. In the process of setting up QnA, we need to set up a QnA service in Azure as well. This is an app service where the Bot deployed and hosted.
Go to the Azure dashboard and set up Cosmos DB instance.
Bot app is a .Net Core Web API project. Hence, it has the same structure as a Web API Project. The Bot client communicates using JSON.
The request received by the BotController and passed over to Microsoft.Bot.Builder.IBot instance. This configuration added in the startup.cs as follows,
The IBot instance has three delegates,
This delegate triggered when a new chat window is created. This is the delegate we use to send a welcome notification to the user.
When implemented in a bot, handles an incoming activity.
This is the delegate that is triggered when a message is sent by the client to the bot.
In our application, we identified 4 chat states which are,
This is the initial state. User information is when we capture the user’s name and email and show a welcome message.
Chat information is when we capture user’s requests and send the data to the QnA service. The QnA service would return either response or prompts. This goes on until the user receives the answer he is looking for or he decides to create a support ticket.
User feedback when we capture whether the user’s request answered properly or whether a support ticket needs to be created. When the support ticket created we log the entire chat history in the ticket.
The support ticket is the step of creating a support ticket in the ticketing system( https://www.servicenow.com/) based on user feedback.
Source code can be downloaded from here. Once you download the source code you should do the following.
#Chatbots #Azure #Luis #Microsoft Bot Framework
Authored by Pragash Rajarathnam @ BISTEC Global
The concept of ETM is a strategic advantage that has been lost in the chaos of offshoring. The concept of offshoring started in India more than 3 decades ago and already spread across many countries across the globe.
So what is ETM and why it is different?
I cite this article in Forbes talking about this concept without the branding in 2010. Now it is reality!
Imagine the team in your head office, spread throughout the globe. This gives the ability to extend your team to reach the human talent that is not available locally, but you can find through a partner like BistecGlobal.
Having a remote team in an offshore context can be a strain on you mainly because of the lack of transparency.
Most often offshore providers use the team on multiple projects and you may find yourself working with different people than who were initially assigned to you just when you were just getting started.
ETM takes the best offshoring minus the bad experience. It gives you the control you need while you retain better financial and logistical benefits. It provides you total integration with the remote teams and also with your business.
With ETM you are able to synchronize all of your “well-tuned” processors, practices and values, into your Extended Team making it a fully functional branch office with a similar design and identity.
“At Bistec Global, we’ve had different experiences working with a verity of engagement models, including ETM and we thrive in this particular environment, and here’s why”
You have the flexibility where you can apply any time or task-driven mechanism/tool to your extended team. Thereby measuring performance with 100% transparency and synchronization.
You might have a successful project that your in-house team is working on and you plan on them continuing to complete it. You can delegate all the new projects to your extended team.
Or you can focus on moving some of the work your home team is doing to the ETM while the home team takes on high-value customer-centric work.
Not being tied down to a single location is a great thing. By using the ETM you can have a team in another location of the world, with a wider knowledge base of technologies, local markets, and other skills that your business can benefit from.
Having different kinds of people working globally, using their knowledge, will lead to creating a startup environment that can take a solution or application on a global market easier than having a centralized local team.
An extended offshore team can save time and money and yet give the expertise that you need to make your application or solution work.
The extended team is involved in the complete cycle of a job/project, not just an isolated part of it which makes for better transition and continuity.
Your corporate image should reflect through all your products and service. This can only be achieved by extensive and continuous team training throughout the company and your extended team…
An extended team is part of a bigger team and employees associate better with their job roles with larger companies which reduces the effect on turnover and dependencies.
By doing so, leads to a steady workflow and becomes resilient to changes in the cooperates which otherwise would be difficult to manage.
A global team means that you can easily have a presence in more than one market. Of course, in today’s world, you can have global reach just by having a great solution and also great marketing, but having people in different parts of the world, that knows the local market and the local mentality can indeed help you build a market reach faster.
Your business generally requires extended hours of work which can result in overworked and demotivated employees. Moreover, the overlap of work hours with your extended teams is likewise important and the part that does not overlap will work to your advantage when planning your project delivery, especially at peak times.
The use of a global talent pool has gained popularity over the last decade. The Extended Team Model is the next stage of outsourcing. Affiliation of your business rather than a group of contractors. ETM offers more flexibility and a new level of dedication. Your extended team may extend according to the objectives set and the resources you require while maintaining your core values and identity.
Over the years, offshoring has gained much popularity in the business world as a means of obtaining services from external parties or companies based in another country. However, many companies who jump on the bandwagon with a lot of expectations get quickly frustrated.
This is mainly because those companies do not spend enough time on due diligence before starting with offshore engagements. Here we list the 5 main mistakes that companies tend to make when starting on an offshore engagement, and how they often lead to its complete downfall.
1. Starting without an initiation workshop
Minimize the assumptions
One can never accurately imagine what another person needs and wants without engaging the latter in the initial stages of setting up a new offshore service delivery engagement. One of the most effective ways to accomplish this is to conduct an initiation workshop with the participation of all stakeholders.
At first, this may sound like an expensive activity, especially considering this is the start of a fresh new engagement in another country. However, this is an activity where money is well spent; in that, it will save much more money, resources, time, and stress in the long run.
Through brainstorming sessions, discussions, sketching, presentations, and more during initiation workshops, the key factors that need to identified are as follows.
· Primary goals and expectations
· Working model
· Key criteria for success
It is important to get all stakeholders for this initiation workshop (based on the relevance of topics discussed and the workshop schedule) from project/product owners and sponsors, senior management, key team members (both onsite and offsite), etc. The few days committed for this workshop will help identify the main goals and expectations, eliminate any misunderstandings and serious concerns that either party may have along with the assumptions that people naturally tend to make which are far from reality.
While these sessions could held at either location, the customer’s location would be preferable for the initiation workshop because for example,
· All the stakeholders are easily accessible
· Start building a good rapport with all onsite team members
· Observe the currently in place systems and processes
2. Starting without getting involved in selecting the right team members
The customer is onsite, while you are not!
Many offshore service providers have found that it is very important to engage their customers in the process of selecting or hiring members for the team. These are the people who are readily accessible to the customer’s onsite team.
We can assume that stakeholders at this point have reached a comfortable and effective collaboration model. Once the customer involved with selecting their offshore team and the collaboration model is in place, this helps everybody to build a good rapport and gel well with each other. This will go a long way in ensuring a successful end result for all stakeholders.
3. Starting without investing in proper infrastructure
An effective and efficient collaboration model is the key to running any successful operation today. In the context of an offshore engagement, where the various stakeholders are physically unable to meet face to face regularly, excellent telecommunication equipment must be in place to facilitate the communication process. This includes both communication hardware and software that is capable of handling large volumes of data while maintaining high quality. A high-speed, high capacity broadband internet link is of absolute need, in ensuring that voice and video conferencing carried out effectively.
Anyone who has participated in an online video conference call will know how difficult it is to concentrate on the discussion at length. This difficultly arises as a result of the lack of face-to-face presence of the participants. This is an unavoidable complication in the offshore service delivery model, but this is a difficulty that can and should eased as much as possible with the use of the best quality communication equipment and services that can obtained. Such equipment includes powerful enough computers, regularly updated communication software, a sound-proof meeting room with proper lighting, sensitive multi-way microphones, and sound systems in addition to a fast-broadband link.
It has been noted one too many times that many companies who go offshoring do not give the due prominence that communication equipment deserves when setting up budgets and investments for their offshore engagements.
4. Starting without budgeting for offshore team visits and vice versa
Subsequent to the initiation workshop, periodic visits between the onsite and offshore teams are vital in ensuring a successful offshore engagement. To be clear, both teams must visit each other. This gives invaluable insights into the social and cultural differences of the other team, which would otherwise have never seen nor understood. And it is the sensitivity shown towards these differences that could help make or break the working dynamics between the two teams.
Also, in certain offshore engagements, it is necessary for the offshore team to visit the customer site and experience the use of the systems they build in real production environments other than simulators/labs maintained offshore. Say, for example, if the offshore team tasked to automate the operations of a robotic manufacturing line, then the offshore team must visit the production environments and experience how the system behaves in reality rather than only working with simulators on offshore labs.
5. Underestimating the impact to the customer organization
For those customers who are beginning their very first offshore engagement, it may not strike them as important to pay close attention to the differences that they would have when compared to the offshore team. The customer’s team would have a particular modus operandi, which has proven itself effective and is likely to think that there is no need to tamper with a system that already works.
While this may well be true, it does not mandate that the same modes and methods would work for the offshore engagement as well. The workings of two different nations will always have their differences. A simple example would be around national and religious holidays in each nation, and how team members celebrate these events. For example, in Myanmar, the water festival held in April is the country’s biggest festival. Three days of holidays declared, during which it is near impossible to expect anyone to be available for any kind of work. It is important then, for both the onsite and offshore teams to be aware of this, and ensure that work, calls, meetings, reports, etc. are planned well in advance or beyond these holidays. The mid-April new year celebrations in Sri Lanka are a similar example.
In addition, it’s important for the onsite team to understand how those of the offshore team address their work ethic. Should any differences arise, it is important to carry out discussions and orientation training in order to agree upon a common working model that is agreeable to both.
A little advance planning and preparation can go a long way in ensuring that working offshore becomes beneficial to all parties concerned. While none of the factors listed above are of a technically complex nature, they are crucial soft components that must addressed, and periodically monitored in order to make sure that all stakeholders benefit from any offshore engagement.
Do you agree? Would like to hear your thoughts as well.
success in business and in life.
We are here to help and support you.
If you believe that we can help you or your businesses requirements - do feel free to drop your details here and don’t hesitate to give us a ring.
Connecting with us on LinkedIn works best.