DAX calc for Average! HELP!!

[Anonymous]

Hi all,

I have a quick question about DAX Calc.

I need to populate a column that shows average headcount.

I have a table with 'All Master Team Detail Data'[month] and 'All Master Team Detail Data'[employee number] to count employee headcount. To get the average headcount, I need to count the headcount of each month from 2022/12 to the most recent month data, which is 2023/09. it is not 12 month cycle, it is 13 month cycle (always include last year December) because I need to count the month from December 2022. what is the DAX going to be for the average headcount?

Lets say one of my property has 20 HC for Dec, 100 HC for Jan, 150 for Feb, 200 for Mar, 500 for Apr, 100 for May, 200 for June, 100 for Jul, 230 for August and 400 for Sep. It is total of 2000 HC. The calc is = add them all and divide by 10 which is 200. This 200 is for September Avg Headcount. August Avg Headcount will be 9/1400=155.55.

The calc is simple but I am newbie to the DAX world and have no idea how that works in DAX.

Please help!!!!

[FreemanZ]

hi @Anonymous ,

how does your [month] column look like? post some sample data please

[Anonymous]

@FreemanZ 

They are 'All Master Team Detail Data'[month] and 'All Master Team Detail Data'[employee number] .

and please check below pic for your references! please help!

[FreemanZ]

hi @Anonymous ,

supposing you have a data table like:

date	EmployeeNumber
12/1/2022	1
1/1/2023	1
1/1/2023	2
2/1/2023	1
2/1/2023	2
2/1/2023	3
3/1/2023	2
3/1/2023	3
3/1/2023	4
4/1/2023	2
4/1/2023	3
4/1/2023	4
4/1/2023	5

try to plot a table visual with the date column and a measure like:

Measure = 
VAR _currentdate = MAX(data[date])
VAR _list =
    CALCULATETABLE(
        VALUES(data[date]),
        data[date]<= _currentdate
            &&data[date]>=DATE( YEAR(_currentdate)-1,1,1)
    )
VAR _result =
    AVERAGEX(
        _list,
        CALCULATE(COUNT(data[EmployeeNumber]))
    )
RETURN _result

it worked like:

[Anonymous]

Hi FreemanZ, I have applied this measure and it works great, but I have a data from 2022 and I need to see the data by YTD. 2022 and 2023 can't be stacked together since it needs to be YTD. Is there any more DAX that needs to be added in this calc?

[AllisonKennedy]

@Anonymous  do you have a dimDate table? You could get the filter to 2022/12 by using a slicer, then calculate the average using averagex:

[Head count] = DISTINCTCOUNT('All Master Team Detail Data'[employee number] )

Average Head Count = AVERAGEX( Values(dimDate[month year]), [head count] )

[Anonymous]

@AllisonKennedy 

I do not have dimDate table. Provided above is what I have!

I am struggling with creating DAX calc for this AVG headcount..

Can you please help with creating DAX calc?