***** Learning Power BI? ***** FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax FREE - Power BI Resources - enterprisedna.co/power-bi-resources FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download/ Enterprise DNA Membership - enterprisedna.co/membership Enterprise DNA Online - portal.enterprisedna.co/ Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/ ***** Related Links ***** How To Work With Multiple Dates In Power BI - blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/ Master Virtual Tables In Power BI Using DAX - blog.enterprisedna.co/working-with-virtual-in-memory-tables-in-power-bi-using-dax/ Counting Customers Over Time Using DISTINCTCOUNT In Power BI - blog.enterprisedna.co/counting-customers-over-time-dax-example-in-power-bi/ ***** Related Course Modules ***** Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations Business Analytics Series - portal.enterprisedna.co/p/business-analytics-series Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations ***** Related Support Forum Posts ***** Staff Population - forum.enterprisedna.co/t/staff-population/2673 Help With Adapting Current Staff Calculations - forum.enterprisedna.co/t/help-with-adapting-current-staff-calculations/6970 Date Stamp Weekly Excel Import - forum.enterprisedna.co/t/date-stamp-weekly-excel-import/6116/4 For more staff population support queries to review see here…. - forum.enterprisedna.co/search?q=staff%20population
Perfect!! I've been struggling for hours to resolve a similar issue regarding the tracking of customer complaints, this answers all my questions. I had not considered deactivating the date relationship. Thank you.
Hi John Russell, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Thanks this is very helpful... Just a note for hr folks out there, we might need to add additional measures or identifiers to employee new hires/rehires that have multiple "start dates" as well as "leaving dates".
Hi Orangutanklaus, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hi Nader, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
It was so simple (in terms of logic) and so awsome on the other side!! It is very helpful not only for staff counting :) Probably the natural follow up question here is: how to calculate average FTE on a monthly / annual basis (respecting two factors - people joining / leaving in the middle (or so) of the period and e.g. part-time jobs). Much appreciate !
Thanks, this is a very elegant solution. I wonder whether you have any advice on how to generate a dynamic measure for the tenure groups (length of time with company), and use the measure as a legend to break down the line chart into separate lines for each group?
Hello @lijunchen, Power BI doesn't have the option to use a measure as a legend in any chart. Moreover, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our UA-cam channel and LinkedIn group to keep posted on the latest data skills and tools updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/
This is so well explained, like so many of your tutorials on UA-cam. Thank you very much Sam. One thing I am a little puzzled about though is the Total for the Current Staff column. It states 170 but it is not obvious to me how this 170 is related to. It would be great if you could shed more light on this. Many thanks.
Hello Maggie, Thank You for posting your query onto our channel. Well if you want to check how actually the figure 170 was derived then you can use the below provided formula - No. of Employees Joined = CALCULATE( COUNTROWS( 'Staff Population' ) , USERELATIONSHIP( Dates[Date] , 'Staff Population'[Start Date] ) ) Then in order to find the No. of Employees who have left the organization over a period of time. Below is the formula provided for the reference - No. of Employees Left = CALCULATE( COUNTROWS( 'Staff Population' ) , USERELATIONSHIP( Dates[Date] , 'Staff Population'[End Date] ) ) And lastly to find out the No. of Employees currently employed in our organization then use the below formula provided for the reference - No. of Employees Currently Employed = [No. of Employees Joined] - [No. of Employees Left] Hoping this clears your doubt. You can likewise go to Enteprise DNA Forum to search for topics related to your query. forum.enterprisedna.co/ Thanks and Warm Regards, Enterprise DNA
Hi Gerard, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Thanks for this! I'm having trouble working it out in my model. Rather than employees, I'm counting student enrollment. I have a table with enroll start and end dates, but i need to filter it by a fixed dates in my school year table. Each row represents a school year, and has a start and end date, so i don't need the max/min functions but i don't seem to be able to reference that second table w/o some sort of function prefacing it. I'm a DAX novice....
Thank you for the video, very helpful. I have tried this approach and it worked for earlier years, however for the current year for some reason in does not want to consider those employees that were hired during the last month, as well as those, who were terminated during the last month. What could be the reason? What would you recommend to try? Thank you in advance!
Hello Tamila, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. It's always difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful! If you haven't yet, you can subscribe to our UA-cam channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hello! Thank you for this! I can't seem to get the calculations to factor in if the employee starts/terms mid month - hoping to have 0.5 FTE if a fulltime employee starts on the 16th, for example. thanks for the help!
Can we get a peek at your dates table? I cannot get this to work for me but I'm not sure I'm diagnosing correctly w/o a glance at how your dates table is set up.
Hello Hitesh Dangodra, thank you for posting your query onto our channel. Well, you can try out the following concept i.e. Current Staff divided by the Total Number of Months. And the formula will be as follows - Average Current Staff = DIVIDE( [Current Staff] , DATEDIFF( MIN( 'Staff Population'[Start Date] ) , MAX( 'Staff Population'[End Date] ) , MONTH ) , 0 ) For furthermore queries, you can also reach out to us onto our Community Forum by providing the description of the problem that you're facing along with the working of the PBIX file as well as by providing the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! forum.enterprisedna.co/
Hi Sam, I have checked manually for Current Staff in Dec 2019, and it got result: 101 employees, while yours (in minute 8:04) was 102 employees, means the end date in Dec 2019 have been missed count. How to solve this?
I have the same results, 101 employees in December 2019. I think this formula counts only hired people per month and not hired minus leaft people per month. My calculus staring different from May 2019, where there are the first time peole leaving in the table (3 people leaving in May 2019).
Hello Nupur Sood, thank you for posting your query onto our channel. Well, when you've datetime combined into the one single column in that case, it's always recommended to split them into two individual columns i.e. one column for date and one separate column for time. Once you split them then you'll be able to perform these calculations and than you can simply insert the time dimension into the visual in order to have analysis at a more granular level. We're also providing few of the links from our Community Forum where similar questions were posted by the members and how the solutions were provided to them. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html forum.enterprisedna.co/t/separating-a-date-time-column-to-date-column-and-time-column-without-breaking-query-folding/12546 forum.enterprisedna.co/t/linking-time-table-with-dates-in-powerbi/14066/3
In my employee census table I have 3 dates: Hire date = (original hire date) / EndDate = (term date or null) / ReHire Date = ( date previous employee was rehired or null). I'm having difficulty adding the rehire date into the formula. How could I factor this in as well.
Thanks a lot for this video. I just implemented it, but have an issue when people are working part time (0.5 FTE), or people started in the middle of the month (not to be counted as 1FTE for that month) Do you have a solution?
Hi yen-ly, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Subscribe to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hello Totopo ARG, thank you for posting your query onto our channel. Well we're mainly focused onto the Power BI related content so not sure we can provide you the results pertaining to any other tools or software. For furthermore queries relating to the Power BI, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/
Hi Sam, this is great! Thank you so much. Also, how could I have the "total staff costs" for the periods (day/month/quarter/year) given the salary of each staff in the same table? Thanks in advance!
Hi Gustavo, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Subscribe to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hi Sam, thanks for all your content! Its always so helpful! I have one question. In my case I have one more column with the workload. So in order to calculate the amount of FTEs, i would like to take the workload (for instance 50%) into the calculation.. So that a part-time person is not weighted the same as a full-time employee. How could I add this? Thanks in advance and keep inspiring!
Hello J Medici, Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it. And we're so glad that you're finding our content so helpful in your learnings. Thank You so much for your valuable feedback!!! Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Also, make sure that you're subscribed to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Thanks - very useful. With 2 inactive relationships to the date table, can this same table be used to show new hires (by start date) and staff attrition (by end date)?
Hi, thanks a lot for your explanation. Very helpful. One thing I did note is that incorrect numbers get thrown out if you create multiple hierarchies (eg. if you want to see at the Quarterly level or Yearly level). I think the problem is that the filter picks up all active employees at any time in that timeframe (eg. if looking yearly, and employee was active in January but not in Feb, they will still get picked up). Any help?
Hello Siddharth, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well, your question as well as the results that you're trying to achieve is not clear to us here. Also, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Also, make sure that you're subscribed to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Thanks a lot for the video, I used it to measure total active events during time, same as total number of staff population over time. Question, is it possible to relate the measurement to the staff table? I'm using stacked area chart, and I want to click on specific spot in time and filter a table below, showing staff name, start and end date. Thanks again. Victor
Hey Sam, could this same model be used on a log in table, where I want to track how many users have logged in at any given hour for a specific set of days?????
Hello whitbywv, Thank You for posting your query onto our channel. Well in this case the context was to calculate the total number of staff over a period of time so it had two columns and they were i.e. Start Date and End Date. Now, in your case, the context is to identify the numbers of users logged in and logged out at any given hour so you shall have the two columns on an hourly basis or of similar nature to track the Login and Login Out timings which will help to identify the numbers of users logged in for a single session. Lastly, by following the similar patterns of the formula you can have the desired result that you're looking for. If this doesn't answer your query then you can post the question on the Enterprise DNA Community Forum. Below is the link provided for the reference - forum.enterprisedna.co/ Hoping you find this useful🙂. Thanks and Warm Regards, Enterprise DNA
Hi! Thanks for this great video! It was very helpfull, I was able to get the right results on a table. However, once I tried a column or a line chart the results were completely different: It showed the same result for each month of every year and the values were 3x higher. I am not sure what I am doing wrong.
Hello Hitesh Dangodra, thank you for posting your query onto our channel. We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA UA-cam channel pertaining to topic - "Rolling or Moving Average". For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! forum.enterprisedna.co/ blog.enterprisedna.co/showcasing-trends-using-moving-average-techniques-in-power-bi/ blog.enterprisedna.co/calculating-a-rolling-average-in-power-bi-using-dax/ ua-cam.com/video/MHKPfoNEyDU/v-deo.html ua-cam.com/video/MLA_9yQRJhM/v-deo.html ua-cam.com/video/mrGhHBpFXIQ/v-deo.html
Hi Hitesh Dangodra, thanks for posting your comment here in the video. This topic has been discussed several times in Enterprise DNA Forum. You might get the answers you are looking for. If you want to check it out here is the link: forum.enterprisedna.co/search?q=calculate%20Rolling%20count
First, thanks, Sam. Very clear explanation. Second, a question: MS docs say VALUES returns unique values. So, if that is true, wouldn't the formula fail to count employees who were hired on the same day? E.g. three employees with start dates of 2019-01-01, 2019-01-02 and 2019-01-02 would return TWO rows instead of the desired three. No? I'm new to DAX so please use small words when clarifying. :-) If this "DISTINCT" behavior is right, should we use ALL instead? docs.microsoft.com/en-us/dax/values-function-dax
same result can be achieved with distinct count dax function why you write this formula i do not understand i try and same result come Current staff= distinctcount('stafffpopulation' [id])
*WHAT IF* THE COUNT OF EMPLOYEES IS NOT 1, say in case of Trade resource. EXAMPLE 40 Carpenters working for a period of 4 months. From 1st jan 22 to 30 April 22 Weekly distribution is required considering 2 Weekend every week A curve of distribution to be considered as per following distribution. 100% = 5%, 7%, 10%, 15%, 30%, 15%, 10%, 5%, 2%, 1%. Special holidays to 5 days to be considered too from 3rd march to 7th mrach 22. If anyone can Please reply. Or please make a video on it
Hello raheem0046, It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful! You can subscribe to our UA-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
***** Learning Power BI? *****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE - Power BI Resources - enterprisedna.co/power-bi-resources
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download/
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Online - portal.enterprisedna.co/
Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/
***** Related Links *****
How To Work With Multiple Dates In Power BI - blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/
Master Virtual Tables In Power BI Using DAX - blog.enterprisedna.co/working-with-virtual-in-memory-tables-in-power-bi-using-dax/
Counting Customers Over Time Using DISTINCTCOUNT In Power BI - blog.enterprisedna.co/counting-customers-over-time-dax-example-in-power-bi/
***** Related Course Modules *****
Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations
Business Analytics Series - portal.enterprisedna.co/p/business-analytics-series
Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations
***** Related Support Forum Posts *****
Staff Population - forum.enterprisedna.co/t/staff-population/2673
Help With Adapting Current Staff Calculations - forum.enterprisedna.co/t/help-with-adapting-current-staff-calculations/6970
Date Stamp Weekly Excel Import - forum.enterprisedna.co/t/date-stamp-weekly-excel-import/6116/4
For more staff population support queries to review see here…. - forum.enterprisedna.co/search?q=staff%20population
Perfect!! I've been struggling for hours to resolve a similar issue regarding the tracking of customer complaints, this answers all my questions. I had not considered deactivating the date relationship. Thank you.
Glad it helped!
Found the video through your blog. Just excellent, solved my problem and made me look awesome in the process, thanks.
Hi John Russell, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Thanks this is very helpful...
Just a note for hr folks out there, we might need to add additional measures or identifiers to employee new hires/rehires that have multiple "start dates" as well as "leaving dates".
Thanks Sam, just what I was looking for today.
Amazing and simple overview! Total lifesaver, so many vids/posts etc massively over complicate this but yours was great :-)
Glad you liked it!
Finally fount what I was looking for!! Thx a lot! :)
Hi Orangutanklaus, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Great example ! Thanks for sharing Sam !
Supercool! Sam makes everything looks easy. Very nice explication of a very common pattern.
Thanks for the great explanation
Hi Nader, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
It was so simple (in terms of logic) and so awsome on the other side!! It is very helpful not only for staff counting :) Probably the natural follow up question here is: how to calculate average FTE on a monthly / annual basis (respecting two factors - people joining / leaving in the middle (or so) of the period and e.g. part-time jobs). Much appreciate !
Glad it was helpful!
Thanks, this is a very elegant solution. I wonder whether you have any advice on how to generate a dynamic measure for the tenure groups (length of time with company), and use the measure as a legend to break down the line chart into separate lines for each group?
Hello @lijunchen,
Power BI doesn't have the option to use a measure as a legend in any chart. Moreover, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our UA-cam channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
This is so well explained, like so many of your tutorials on UA-cam. Thank you very much Sam. One thing I am a little puzzled about though is the Total for the Current Staff column. It states 170 but it is not obvious to me how this 170 is related to. It would be great if you could shed more light on this. Many thanks.
Hello Maggie,
Thank You for posting your query onto our channel.
Well if you want to check how actually the figure 170 was derived then you can use the below provided formula -
No. of Employees Joined =
CALCULATE( COUNTROWS( 'Staff Population' ) ,
USERELATIONSHIP( Dates[Date] , 'Staff Population'[Start Date] ) )
Then in order to find the No. of Employees who have left the organization over a period of time. Below is the formula provided for the reference -
No. of Employees Left =
CALCULATE( COUNTROWS( 'Staff Population' ) ,
USERELATIONSHIP( Dates[Date] , 'Staff Population'[End Date] ) )
And lastly to find out the No. of Employees currently employed in our organization then use the below formula provided for the reference -
No. of Employees Currently Employed = [No. of Employees Joined] - [No. of Employees Left]
Hoping this clears your doubt.
You can likewise go to Enteprise DNA Forum to search for topics related to your query.
forum.enterprisedna.co/
Thanks and Warm Regards,
Enterprise DNA
Thank you..I was looking out for a similiar measure for one of my analysis.
Glad it was helpful! Please watch out for our 30 for 30 this month, that's 30 videos for 30 days! Don't forget to subscribe to our channel :)
Thanks for a very good tutorial. You are awesome.
Thanks D P, please don't forget to share the video and subscribe to our channel to be updated with all our upcoming videos.
Super examples keep it up, can u do some "overtime" examples
Bruh, this is lit. You saved my ass today. Lets GOOOOOOOOOO!!!!!!!! *instant Subscriber ALERT*
Hi Gerard, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Thanks for this! I'm having trouble working it out in my model. Rather than employees, I'm counting student enrollment. I have a table with enroll start and end dates, but i need to filter it by a fixed dates in my school year table. Each row represents a school year, and has a start and end date, so i don't need the max/min functions but i don't seem to be able to reference that second table w/o some sort of function prefacing it. I'm a DAX novice....
Thank you for the video, very helpful. I have tried this approach and it worked for earlier years, however for the current year for some reason in does not want to consider those employees that were hired during the last month, as well as those, who were terminated during the last month. What could be the reason? What would you recommend to try? Thank you in advance!
Hello Tamila,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
It's always difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful! If you haven't yet, you can subscribe to our UA-cam channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Hi,
this is very much helpful this gives the opening headcount of Month
what if i need the closing headcount in month. Please help me on this
Fantastic!
Sam, how do i get the total head count at any given time using date(year_month) slicer, similar to a running total.
Hello! Thank you for this! I can't seem to get the calculations to factor in if the employee starts/terms mid month - hoping to have 0.5 FTE if a fulltime employee starts on the 16th, for example. thanks for the help!
Can we get a peek at your dates table? I cannot get this to work for me but I'm not sure I'm diagnosing correctly w/o a glance at how your dates table is set up.
Excellent|! Thanks Sam!
Thank you so much, please help how calculate average of current staff each month, that will help me lot
Hello Hitesh Dangodra,
thank you for posting your query onto our channel.
Well, you can try out the following concept i.e. Current Staff divided by the Total Number of Months. And the formula will be as follows -
Average Current Staff =
DIVIDE( [Current Staff] ,
DATEDIFF( MIN( 'Staff Population'[Start Date] ) , MAX( 'Staff Population'[End Date] ) , MONTH ) ,
0 )
For furthermore queries, you can also reach out to us onto our Community Forum by providing the description of the problem that you're facing along with the working of the PBIX file as well as by providing the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/
Hi Sam, I have checked manually for Current Staff in Dec 2019, and it got result: 101 employees, while yours (in minute 8:04) was 102 employees, means the end date in Dec 2019 have been missed count. How to solve this?
I have the same results, 101 employees in December 2019. I think this formula counts only hired people per month and not hired minus leaft people per month. My calculus staring different from May 2019, where there are the first time peole leaving in the table (3 people leaving in May 2019).
Thank You for this video. I am trying to work this for a datetime field and the logic doesn't seem to work. Would you expect it to work similarly?
Hello Nupur Sood,
thank you for posting your query onto our channel.
Well, when you've datetime combined into the one single column in that case, it's always recommended to split them into two individual columns i.e. one column for date and one separate column for time. Once you split them then you'll be able to perform these calculations and than you can simply insert the time dimension into the visual in order to have analysis at a more granular level.
We're also providing few of the links from our Community Forum where similar questions were posted by the members and how the solutions were provided to them.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Also make sure that you're subscribed to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Happy Learning!!!
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
forum.enterprisedna.co/t/separating-a-date-time-column-to-date-column-and-time-column-without-breaking-query-folding/12546
forum.enterprisedna.co/t/linking-time-table-with-dates-in-powerbi/14066/3
In my employee census table I have 3 dates: Hire date = (original hire date) / EndDate = (term date or null) / ReHire Date = ( date previous employee was rehired or null). I'm having difficulty adding the rehire date into the formula. How could I factor this in as well.
Thanks a lot for this video. I just implemented it, but have an issue when people are working part time (0.5 FTE), or people started in the middle of the month (not to be counted as 1FTE for that month)
Do you have a solution?
Hi yen-ly,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful. Subscribe to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Thanks Sam. Do you know how can i do this in tableau?
Hello Totopo ARG,
thank you for posting your query onto our channel.
Well we're mainly focused onto the Power BI related content so not sure we can provide you the results pertaining to any other tools or software.
For furthermore queries relating to the Power BI, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful.
Happy Learning!!!
forum.enterprisedna.co/
Hi Sam, this is great! Thank you so much. Also, how could I have the "total staff costs" for the periods (day/month/quarter/year) given the salary of each staff in the same table? Thanks in advance!
Hi Gustavo,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful. Subscribe to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Hi Sam, thanks for all your content! Its always so helpful!
I have one question. In my case I have one more column with the workload. So in order to calculate the amount of FTEs, i would like to take the workload (for instance 50%) into the calculation.. So that a part-time person is not weighted the same as a full-time employee. How could I add this?
Thanks in advance and keep inspiring!
Hello J Medici,
Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it. And we're so glad that you're finding our content so helpful in your learnings. Thank You so much for your valuable feedback!!!
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful. Also, make sure that you're subscribed to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Thanks - very useful. With 2 inactive relationships to the date table, can this same table be used to show new hires (by start date) and staff attrition (by end date)?
Yes you just need to utilise the USERELATIONSHIP function to turn the relationships on virtually.
@@EnterpriseDNA Example pretty pretty pleaaase!
Hi, thanks a lot for your explanation. Very helpful. One thing I did note is that incorrect numbers get thrown out if you create multiple hierarchies (eg. if you want to see at the Quarterly level or Yearly level). I think the problem is that the filter picks up all active employees at any time in that timeframe (eg. if looking yearly, and employee was active in January but not in Feb, they will still get picked up). Any help?
Hello Siddharth,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well, your question as well as the results that you're trying to achieve is not clear to us here. Also, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful. Also, make sure that you're subscribed to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Thanks a lot for the video, I used it to measure total active events during time, same as total number of staff population over time.
Question, is it possible to relate the measurement to the staff table? I'm using stacked area chart, and I want to click on specific spot in time and filter a table below, showing staff name, start and end date.
Thanks again.
Victor
Hey Sam, could this same model be used on a log in table, where I want to track how many users have logged in at any given hour for a specific set of days?????
Hello whitbywv,
Thank You for posting your query onto our channel.
Well in this case the context was to calculate the total number of staff over a period of time so it had two columns and they were i.e. Start Date and End Date.
Now, in your case, the context is to identify the numbers of users logged in and logged out at any given hour so you shall have the two columns on an hourly basis or of similar nature to track the Login and Login Out timings which will help to identify the numbers of users logged in for a single session. Lastly, by following the similar patterns of the formula you can have the desired result that you're looking for.
If this doesn't answer your query then you can post the question on the Enterprise DNA Community Forum. Below is the link provided for the reference -
forum.enterprisedna.co/
Hoping you find this useful🙂.
Thanks and Warm Regards,
Enterprise DNA
Hi! Thanks for this great video! It was very helpfull, I was able to get the right results on a table. However, once I tried a column or a line chart the results were completely different: It showed the same result for each month of every year and the values were 3x higher. I am not sure what I am doing wrong.
Sam, shouldn't the end date be > max dates[date] or blank ?
Hi Matthew, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/t/how-to-calculate-headcount-over-time/2908
Sir how to calculate moving average or rolling average of current staff
Hello Hitesh Dangodra,
thank you for posting your query onto our channel.
We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA UA-cam channel pertaining to topic - "Rolling or Moving Average".
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/
blog.enterprisedna.co/showcasing-trends-using-moving-average-techniques-in-power-bi/
blog.enterprisedna.co/calculating-a-rolling-average-in-power-bi-using-dax/
ua-cam.com/video/MHKPfoNEyDU/v-deo.html
ua-cam.com/video/MLA_9yQRJhM/v-deo.html
ua-cam.com/video/mrGhHBpFXIQ/v-deo.html
How to calculate Rolling count (last 12 month count) of current staff
Hi Hitesh Dangodra, thanks for posting your comment here in the video. This topic has been discussed several times in Enterprise DNA Forum. You might get the answers you are looking for.
If you want to check it out here is the link: forum.enterprisedna.co/search?q=calculate%20Rolling%20count
First, thanks, Sam. Very clear explanation. Second, a question: MS docs say VALUES returns unique values. So, if that is true, wouldn't the formula fail to count employees who were hired on the same day? E.g. three employees with start dates of 2019-01-01, 2019-01-02 and 2019-01-02 would return TWO rows instead of the desired three. No? I'm new to DAX so please use small words when clarifying. :-) If this "DISTINCT" behavior is right, should we use ALL instead? docs.microsoft.com/en-us/dax/values-function-dax
I didnt get staff turnover data correct by using this dax
same result can be achieved with distinct count dax function why you write this formula i do not understand i try and same result come Current staff= distinctcount('stafffpopulation' [id])
*WHAT IF* THE COUNT OF EMPLOYEES IS NOT 1, say in case of Trade resource.
EXAMPLE
40 Carpenters working for a period of 4 months. From 1st jan 22 to 30 April 22
Weekly distribution is required considering
2 Weekend every week
A curve of distribution to be considered as per following distribution.
100% = 5%, 7%, 10%, 15%, 30%, 15%, 10%, 5%, 2%, 1%.
Special holidays to 5 days to be considered too from 3rd march to 7th mrach 22.
If anyone can Please reply. Or please make a video on it
Hello raheem0046,
It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful! You can subscribe to our UA-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/