Calculate Count Between Two Dates - Incorrect on Higher Level of Date Hierarchy

[Anonymous]

Hi all,

I am trying to create a report which shows number of employee terminations during parental leave.

My model is below.

Employee and TimeOffRequest SF tables are joined by EmployeeID, and there is no physical relationship on CalDate table (it is also marked as Date table).

In the report, there are other visuals which rely on CalDate table, but I only use one slicer based on CalDate[Date] (hence no physical relationship on the model).

This is the DAX formula that I use to create the measure:

TerminationCountDuringLeave = 
    CALCULATE(
                DISTINCTCOUNT(Employee[SFUserID]),           
                FILTER(TimeOffRequestSF, 
                       TimeOffRequestSF[LeaveStartDate] <= MAX(CalDate[CalDate])
                       && TimeOffRequestSF[LeaveEndDate] >= MIN(CalDate[CalDate])
                ),
               TREATAS(VALUES(CalDate[CalDate]), Employee[TerminationDate])             
    )

This seems to work fine / giving the correct result on Date level, but when I go to Month level, it is not showing correct result.

For example, as you can see in the pic below, there is no data for February 2019 when the visual showing date-level.

However, when I drill-down to Month level, it is showing some records for Feb 2019 (and other months which not meant to be there).

I created a drillthrough page to see the actual records and found the following for Feb 2019.

So, I think because of the following condition in the DAX measure, when the visual is in Month level, for the first record it says: LeaveEndDate = 6 Feb 2019 >= Min date of february 2019 (= 1 Feb 2019), which is true.

&& TimeOffRequestSF[LeaveEndDate] >= MIN(CalDate[CalDate])

I dont want this to be included (and the second record also) because the termination date is outside LeaveStart and LeaveEnd dates.

How can I achieve the correct results regardless the visual is showing date-level or month-level?

Thanks

[v-yuta-msft]

@Anonymous ,

Change the direction between table "Employee" and "TimeOffRequestSF" from single to both and check if the result is as your expected result.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the reply @v-yuta-msft . Unfortunately, changing to Both direction does not change anything. Still giving the same wrong result. 😞