Average measure and filter context?

[Pbiuserr]

Hello,

I've got three measures - headcount, headcount YTD and average headcount. It looks like they work okay, but not really. In the picture below

have no calendar period selected in the picture above. When I select for instance May 2022 then it becomes

My goal is to have same values so Headcount YTD 24536 and average 4907. How to achieve that? Unfortunately can't share my data

[tamerj1]

@Pbiuserr 
How are they supposed to be calculated to produces these numbers? and what about "Headcount"? What is the current code used in each mesure?

[Pbiuserr]

Hello, 
Headcount is

Headcount = 
VAR MinDate = MIN(Calendar[Date])
VAR MaxDate = MAX(Calendar[Date])
VAR result = 
	CALCULATE(
		DISTINCTCOUNT(MainTable[ID]),
		KEEPFILTERS(
			FILTER(
				ALL(MainTable[Termination Date-Employment Details], MainTable[Hire Date-Employment Details] ),
	            (MainTable[Termination Date-Employment Details] > MinDate || ISBLANK(MainTable[Termination Date-Employment Details])) && MainTable[Hire Date-Employment Details] <= MaxDate
	        )
        ),
        MainTable[Employee Status] = "A",
	)
RETURN
    result

Headcount YTD

Headcount YTD = 
VAR MaxDate = 
            MAX('Calendar'[Date])
VAR CurrentYear = YEAR(MaxDate)
VAR CurrentMonth = MONTH(MaxDate)
VAR ResultByMonths =
        ADDCOLUMNS(
           FILTER( 
            ALL(
                    'Calendar'[Year],
                    'Calendar'[Month],
                    'Calendar'[MONTH NAME]
            ),
                'Calendar'[Year] = CurrentYear &&
                'Calendar'[Month] <= CurrentMonth 
           ),
            "@CountByMonths", [Headcount]
        )
VAR Result = 
                SUMX(
                    ResultByMonths,
                    [@CountByMonths]
                )

RETURN
Result

and Average Headcount is

Average Headcount =
DIVIDE( [Headcount YTD], MONTH(MAX('Calendar'[Date] ) ) )

Hope it helps 

[Anonymous]

Hi @Pbiuserr ,

Please try to update the formula of measure [Headcount YTD] as below and check if you can get the correct result...

Headcount YTD =
VAR MinDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR MaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR CurrentYear =
    YEAR ( MaxDate )
VAR MinMonth =
    MONTH ( MinDate )
VAR MaxMonth =
    MONTH ( MaxDate )
RETURN
    SUMX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year] = CurrentYear
                && 'Calendar'[Month] >= MinMonth
                && 'Calendar'[Month] <= MaxMonth
        ),
        [Headcount]
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file with fake data. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards