Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a dataset that shows a snapshot of the current employees in the company, which means there is no timestamp. Anytime I open the dataset, it shows the employees at the moment. Is it possible to have Power BI automatically calculate the averaged number of employees in the company on the 1st of every month?
If one row represents one employee, the total number of rows will be the number of employees. On the 1st of every month, I would like Power BI to count the number of rows and average them. For instance, on Jan 1st, Power BI might get 11 rows from the snapshot of the dataset. On Feb 1st, Power BI might get 20 rows from the sanpshot of the dataset. I would like Power BI to average 11 and 20, and give me 16 as the result in Feb. By mar 1st, if we get 15 rows, I would like Power BI to average 11, 20, and 16, and give me 16 in Mar.
How do I do that?
Solved! Go to Solution.
I ended up finding the solution here:
https://community.powerbi.com/t5/Desktop/Calculate-HR-turnover-and-show-trend/m-p/28570#M9529
Hi @Anonymous ,
You may create calculated column to get [Year_Month], then create measure to get Average like DAX below.
Column:
Year_Month= FORMAT('Date'[Date],"yyyymm")
Measure :
Average Count =
VAR _Table =
SUMMARIZE (
Table1,
Table1[Year_Mont],
"Count", CALCULATE ( COUNT ( Table1[EmployID] ) )
)
RETURN
AVERAGEX ( _Table, [Count] )
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
If your dataset always gets the data of all employes for each month(data has to be there in daatset to calculate average)
I would use
AVG_Count_Per_month = AVERAGEX ( SUMMARIZE ( Table1, Table1[month-year], Table1[id], "Count", COUNT ( Table1[id] ) ), [Count] )
Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
I don't have timestamp in my dataset. Here is a couple of examples of my dataset.
In Jan, my Power BI will load this dataset. Total number of employee = 2
Employee Code | Hired date | Employee Status |
a123 | 2010/07/01 | A |
a125 | 2015/05/01 | A |
In Feb, my Power BI will load this dataset, and the dataset I showed above (in Jan) will be OVERWRITTEN by this one. Total number of employee = 2.
Employee Code | Hired date | Employee Status |
a123 | 2010/07/01 | A |
a125 | 2015/05/01 | I |
a127 | 2020/02/01 | A |
In Mar, my Power BI will load this dataset, and the dataset I showed above (in Feb) will be OVERWRITTEN by this one. Total number of employee = 3.
Employee Code | Hired date | Employee Status |
a123 | 2010/07/01 | A |
a125 | 2015/05/01 | I |
a127 | 2020/02/01 | A |
a129 | 2020/03/05 | A |
Any suggestions?
I ended up finding the solution here:
https://community.powerbi.com/t5/Desktop/Calculate-HR-turnover-and-show-trend/m-p/28570#M9529
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
71 | |
65 | |
58 | |
50 | |
47 |