Forum Discussion
Trending headcount
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
2 Replies
- AnonymousNot applicable
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
Resolver I
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.