How can I count the numer of rows filtering by two dates?

[Anonymous]

Good afternoon everyone,

This table is named "employees"

So I have the following problem:

The column "Fin.Ult Contr" shows the date when the employee was hired and the column "Nueva Fecha" shows when the same employee was fired. 

I'd like to know how many employees do I had working when I make a date filter selecting a minimum date (when the employees got hired) and a maximum date (when the employees got fired)

Is it necessary to create a calendar table to connect that two columns?

Many thanks in advance,

Kind regards,

Adrian

[v-yuta-msft]

@Anonymous ,

You can create a measure using dax below:

Result =
VAR Min_Date =
    CALCULATE ( MIN ( Calendar[Date] ), ALLSELECTED ( Calendar ) )
VAR Max_Date =
    CALCULATE ( MAX ( Calendar[Date] ), ALLSELECTED ( Calendar ) )
RETURN
    COUNTROWS (
        FILTER (
            employees,
            employees[Fin.Ult Contr] <= Min_Date
                && employees[Nueva Fecha] >= Max_Date
        )
    )

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]

Hi @v-yuta-msft,

I've tried your DAX but as you can see, it's not throwing any data back even when all the dates are selected: 

I don't know but problably it's because of how the tables are linked currently (Fin Ult.Contr & Nueva Fecha Corregida are both linked to Date):

By the way, I've tried the following measure with both of the links being inactive but again, it's not throwing back back the result I'm looking for: 

EMPLEADOS TOTALES =

CALCULATE( COUNTROWS('DETALLES EMPLEADO'); FILTER( VALUES( 'DETALLES EMPLEADO'[Fin Ult.Contr]); 'DETALLES EMPLEADO'[Fin Ult.Contr]<= MAX('CALENDARIO GENERAL'[Date])); FILTER( VALUES('DETALLES EMPLEADO'[NUEVA FECHA CORREGIDA]);'DETALLES EMPLEADO'[NUEVA FECHA CORREGIDA]>= MIN('CALENDARIO GENERAL'[Date])))

Many thanks for your response! 

Hope you can help me..

Cheers! 

Adrian

[v-yuta-msft · Accepted solution]

@Anonymous ,

I apologize for the mistake above, please modify the measure as below

Result =
VAR Min_Date =
    CALCULATE ( MIN ( Calendar[Date] ), ALLSELECTED ( Calendar ) )
VAR Max_Date =
    CALCULATE ( MAX ( Calendar[Date] ), ALLSELECTED ( Calendar ) )
RETURN
    COUNTROWS (
        FILTER (
            employees,
            employees[Fin.Ult Contr] >= Min_Date
                && employees[Nueva Fecha] <= Max_Date
        )
    )

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.