DAX Filter Between Dates

[Anonymous]

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?

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Share some data in a format that can be pasted in an MS Excel file and show the expected result.

[lbendlin]

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)