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,
I want to vaildate the Headcount with a salary in the Salary History table which lists all the salary changes for employees over the year and includes their Salary Change Date and Salary End Date.
I've created the DAX formula below which is bringing back 11009 and the Date filter on this page is set on 01/05/23 - 31/05/23
Salary Headcount = CALCULATE(COUNTROWS(SalaryHistory),
FILTER(VALUES(SalaryHistory[SALARY CHANGE DATE]), SalaryHistory[SALARY CHANGE DATE]<=MAX(DimDates[Date])),
FILTER(VALUES(SalaryHistory[SALARY END DATE]), SalaryHistory[SALARY END DATE] >=MAX(DimDates[Date])))+0
I believe the above DAX is filtering on dates that are before or equal to the 31/05/23 in the Salary Change Date field and then filtering on dates that are after and equal to 31/05/23 in the Salary End Date field to get a headcount of those with a Salary.
However when I try to validate this in Excel using the same data set (Salary History) and applying the same date filters in the same fields I get back 11193.
Can some one please explain why I'm getting a lower number in DAX and what the formula is doing in that table?
Hi @lbendlin @Ashish_Mathur ,
Thanks for your replies, it transpired that the excel file I was validating the results against changed the dates to the US format instead of the UK format once I corrected this the figures stacked up so my DAX measure was correct in the end.
Hi,
Share some data in a format that can be pasted in an MS Excel file and show the expected result.
The answer depends on your data model. Please provide more details. Here is a refactored version of your measure.
Salary Headcount =
var md = MAX(DimDates[Date])
return CALCULATE(COUNTROWS(SalaryHistory),
SalaryHistory[SALARY CHANGE DATE] <= md,
SalaryHistory[SALARY END DATE] >= md)
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |