Cumulative measure in past years and months

[bebeto]

Hello,

I am buiding a report from an ERP tables. I have an employees table in which I know data_from and data_to. It also has an Active columns.

What I want to paint in a chart is the number of employees active by past years and months. I have created a DimDate table and related with my employees table, 2 relationships, with date_from and date_to.

¿How can I get this information?

Thanks in advance!

[v-haibl-msft · Accepted solution]

@bebeto

Thanks for the sample data. I create a calendar table and a measure with following formula.

But I’m confused that why there is only 1 active employee (lker) in April. Roberto’s date_to is 30/04/2016 which is the last day in April.

DimFecha = CALENDAR ( MIN ( Empleados[Fecha_incorporacion] ), TODAY () )

EmpleadosActivosPeriodo =
CALCULATE (
    COUNTROWS ( Empleados ),
    FILTER (
        Empleados,
        Empleados[Fecha_incorporacion] <= MAX ( DimFecha[Date] )
            && Empleados[date_to] >= MAX ( DimFecha[Date] )
    )
)

Best Regards,
Herbert

[v-haibl-msft · Accepted solution]

@bebeto

You can inactive the needed relationships and use USERELATIONSHIP function when you need it. Following two articles may help you.

http://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-a...

http://sqlblog.com/blogs/marco_russo/archive/2010/02/09/how-to-relate-tables-in-dax-without-using-re...

For you second question, please try with following modified measure formula.

EmpleadosActivosPeriodo =
IF (
    MIN ( DimFecha[Date] ) <= TODAY (),
    CALCULATE (
        COUNTROWS ( Empleados ),
        FILTER (
            Empleados,
            Empleados[Fecha_incorporacion] <= MAX ( DimFecha[Date] )
                && Empleados[date_to] >= MAX ( DimFecha[Date] )
        )
    ),
    BLANK ()
)

Best Regards,

Herbert

[bebeto]

I have created the measure as you said:

EmpleadosActivosPeriodo =
CALCULATE
(
COUNTROWS(Empleados);
FILTER(Empleados;Empleados[Fecha_incorporacion] <= MIN(DimFecha[Date]) && Empleados[date_to] > max(DimFecha[Date])
))

But the result is "Blank"

I don't understand "In this case I have a second date table to use on the axis of the visuals, make sure you do not create a relationship between the 2."

I have a DimFecha table (as a calendar) and is related with my employees table with two relationships (date_from and date_to)

What is wrong?

[Vvelarde]

What happen if you change

EmpleadosActivosPeriodo =
CALCULATE
(
COUNTROWS(Empleados);
FILTER(Empleados;Empleados[Fecha_incorporacion] >= MIN(DimFecha[Date]) && Empleados[date_to] <= max(DimFecha[Date])
))

[kdejonge]

The relationships will filter out the data based on the relationship so you should remove the relationships. By thw way the calculation will not give you a cumulative but employees within the time range.