CALCULATE and COUNTROW filter with multiple dates

[Anonymous]

Dear all,

I´m having difficulties in my dax formula using CALCULATE and COUNTROWS as described below.

I have two tables that contains information from employees:

1: SAP_HR-VW_INTEGRANTE_INFO has distinct employees' id values, with date of dismissal (that I´m interested)

2: SAP_HR-VW_Z_FOLHA has multiple employees' id values, with all the earnings each month of the year.

I want to count how many active employees I have for each period.

I´m trying

Count of Active Employees  =
CALCULATE(
COUNTROWS('SAP_HR-VW_Z_FOLHA');
FILTER('SAP_HR-VW_INTEGRANTE_INFO';
'SAP_HR-VW_INTEGRANTE_INFO'[Date of Dismissal] < STARTOFMONTH('SAP_HR-VW_Z_FOLHA'[Period of earnings (date)])
|| 'SAP_HR-VW_INTEGRANTE_INFO'[Date of Dismissal] > ENDOFMONTH('SAP_HR-VW_Z_FOLHA'[Period of earnings (date)])
|| 'SAP_HR-VW_INTEGRANTE_INFO'[Date of Dismissal] = BLANK()
)
)

But it´s not giving me the correct number. I´m guessing it´s not getting the "Period of earnings" for each line.

Thank you,

Eduardo

[TomMartens]

Hey,

what is the relationship between your both tables, with this I mean the relationship in your Power BI data model

SAP_HR-VW_INTEGRANTE_INFO

and

SAP_HR-VW_Z_FOLHA

I have to admit that I'm not familiar with SAP data, but what is the exact content of the column "Period of earnings (date)"?

Did you check the result of your expression by just using
CALCULATE(
  STARTOFMONTH(...)
)

and

CALCULATE(
  ENDOFMONTH(...)
)

Regards

[Anonymous · Accepted solution]

Dear Tom,

Thank you very much. Just by testing ENDOFMONTH and STARTOFMONTH separately I was able to identify the problem.

The content of "Period of earnings (date)" was the first of every month (01/01/2017, 02/01/2017, etc). I needed to get the first and last date of the month of that year. I noticed I missed the ".[Date]". After I included it, I was able to see in a separate table the value, and then into my expression and it worked.

Count Test (End of Month) =
CALCULATE(
ENDOFMONTH('SAP_HR-VW_Z_FOLHA'[Period of earnings (date)].[Date])
)

Best regards,

Eduardo