Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello.
I was hoping you could help me calculate an annual average headcount.
I have a table with all the employees month by month, and I have a date table as well.
The employee table looks like that:
Date ID | Employee ID |
20220101 | 1234 |
20220101 | 1235 |
20220101 | 1236 |
20220101 | 1237 |
20220101 | 1238 |
20220101 | 1239 |
20220201 | 1234 |
20220201 | 1235 |
20220201 | 1236 |
20220201 | 1238 |
20220201 | 1239 |
20220301 | 1234 |
20220301 | 1235 |
20220301 | 1236 |
20220301 | 1238 |
20220301 | 1239 |
20220301 | 1240 |
20220301 | 1241 |
For now I have the employees of January date ID 2022-01-01 (the first 6 rows) next month I will have all the employees again with date id 20220201 (the next 5 rows) etc.
I would like to calculate the average number of employees. For example, for January I will have the 6 employees / 1 = 6
When the February comes: (6 (January) + 5 (February) )/2 (January +February) = 5,5
When the March comes: (6 (January) + 5 (February) + 7 (March) )/3 (January +February +March) = 6
I Managed to do it using the Date ID: DIVIDE(COUNT(Table[Employee ID]), DISTINCTCOUNT(Table[Date ID]),0)
I would like to achieve the same result using the date table. Is it possible?
Thank you in advance!!
Solved! Go to Solution.
Hi,
This is one way to do this:
data:
Dax:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
Try this, create the measure,
Measure =
var _start= VALUE(LEFT(MIN('Table'[Date ID]),4)&"0101")
var _end= MIN('Table'[Date ID])
var _countemployees =
CALCULATE (
COUNT ( 'Table'[Employee ID] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date ID] >= _start
&& 'Table'[Date ID] <= _end
)
)
var _countmonth =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Date ID] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date ID] >= _start
&& 'Table'[Date ID] <= _end
)
)
return DIVIDE(_countemployees,_countmonth)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xiaotang ,
Thank you very much for replying to me. If I am not wrong, there is no use of the calendar table correct?
How can I use the calendar table to have the same results?
Thank you in advance. Your help is greatly appreciated
Hi,
This is one way to do this:
data:
Dax:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
Hi @ValtteriN
First i would like to thank you for your reply and your help.
I have an issue regarding the data type...the one calendar column is type text and the other type number :(...I am trying to resolve it.
Thank you very very much again for your help
Hi,
Try changing the data type here:
Or in the Powerquery:
Or Finally by using functions e.g. VALUES or CONCANETATE
Proud to be a Super User!
Thank you @ValtteriN ,
I managed to resolve my problem by changing your measure:
var maxdate = MAX('Time'[Calendar Date])
var rt =
CALCULATE (
COUNT(Table[Employee ID]), --
ALL('Time'),'Time'[Calendar Date]<=maxdate && Time [Calendar Date]>=min('Time'[Calendar Date]))
var _monthnum = MONTH(MAX('Time'[Calendar Date]))
return
Divide(rt,_monthnum)
Thank you very much again for your help!
User | Count |
---|---|
66 | |
46 | |
20 | |
19 | |
15 |
User | Count |
---|---|
121 | |
41 | |
38 | |
28 | |
23 |