Trending headcount

[CarlBlunck]

Hi all,

I've written the following dax expression to calculate a trending headcount but it is consistently giving 1 less than what the result should be.  Example, result for March 2022= 10,245 but running from source, result for March 2022 - 10,246.

TrendingHeadcount = 
VAR EOM = MAX(dim_PlacementDates[Date])
VAR SOM = MIN(dim_PlacementDates[Date])

RETURN

CALCULATE(DISTINCTCOUNT(fact_PlacementData[WamiKey]),
FILTER(fact_PlacementData,
(fact_PlacementData[HireDate]<= EOM && fact_PlacementData[TerminationDate]>=SOM) || 
(fact_PlacementData[HireDate]<= EOM && fact_PlacementData[TerminationDate]=BLANK())
)
) 

My underlying data basically looks like this:

WamiKey	HireDate	TerminationDate
1	01/01/2022
2	01/01/2020	12/03/2022
3	01/01/2022	01/05/2022

The dim_PlacementDates table the variables are created off is a date table where both the HireDate and TerminationDate columns have inactive relationships with the Date column.

Unable to provide the pbix file sorry.  Any insights into why I am consistently getting 1 less than the expected result would be greatly appreciated!!

Cheers

Carl

[Anonymous]

The measure seems correct to me. Are you sure this isn't happening because of the way the data is imported in Power BI? 

[CarlBlunck]

I was wondering that - I noticed that when the data is refreshing it says there are 69,883 rows.  But when I look at the table after the refresh in the data view, it says there are only 25,737 rows, which I find weird...

Table in the data view matches what I am seeing in SQL.