Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
The measure seems correct to me. Are you sure this isn't happening because of the way the data is imported in Power BI?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |