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]

To close with this question... I need to filter my chart by actual year, now I am doing it adding the year from DimFecha as a filter and typing 2016 manually.

How could I do this filter dynamically?

 

[v-haibl-msft]

@bebeto

You can drag a Slicer into your canvas, then you can select current year 2016 directly.

Or do you want the chart always show the data for current actual year without typing/selecting anything?

Best Regards,

Herbert

[bebeto]

 Yes, I want to show always the data for current year.

I have created a new column "ActualYear" in my DimFecha table to show 1 if the date is from actual year. then, I add this column as a filter in my chart, filtering value = 1. Is it a good solution?

Now, I want to show as a line in the chart the average of active employees from these months... How can I do this? 

[v-haibl-msft]

@bebeto

Please try to change the calendar formula and add a Month column for it as below.

DimFecha = CALENDAR ( "1/1/" & YEAR ( TODAY () ), TODAY () )

Month = MONTH( DimFecha[Date] )

Then create a measure with following formula.

Average_Active = 
AVERAGEX (
    SUMMARIZE (
        ALL ( DimFecha ),
        DimFecha[Month],
        "Active_Count", [EmpleadosActivosPeriodo]
    ),
    [Active_Count]
)

Best Regards,
Herbert

[bebeto]

@v-haibl-msft

I have tried it creating a second DimFecha2 only with data from the beginning of the actual year to today as you said and it works.

But, can I do the same with my existing dimension DimFecha? It contains data from 1940 to 2020, can I filter thar dimension in the calculatios? I have tried with this but it doesn´t work:

Average_Active2 =
AVERAGEX (
SUMMARIZE (
FILTER(DimFecha;DimFecha[Date]<TODAY() && DimFecha[Year]=2016);
DimFecha[Month];
"Active_Count"; [EmpleadosActivosPeriodo]
);
[Active_Count]
)

This shows:

[v-haibl-msft · Accepted solution]

@bebeto

Since you want to always show the data for current year without typing/selecting anything, you need to create this “current year” table to use on the axis of the visuals.

Best Regards,

Herbert

[bebeto]

Ok, and how is this new table refreshed?

[v-haibl-msft]

@bebeto

This new table should be refreshed when you open the .pbix file or refresh the report.

Best Regards,
Herbert

[bebeto]

Yes, but it can be scheduled or the user have to refresh it manually?

[v-haibl-msft]

@bebeto

If you publish this report to Power BI Service, you can use scheduled refresh.

Best Regards,
Herbert

[bebeto]

@v-haibl-msft

As you said, I have to use a new dimension DimFechaActualYear yo show only the data of actual year. Doing this I have a problem, ¿how can I show in a diagram a comparison between this year and the previous year?

The measure that I want to show uses the DimFechaActualYear dimension, so SAMEPERIODLASTYEAR doesn´t work...

[v-haibl-msft]

@bebeto

Do you want to show the number of active employees previous year (Jan to July)? If yes, please try to create following two measures.

LatestDateLY = 
VAR LatestCY =
    MAX ( DimFecha[Date] )
RETURN
( DATE ( YEAR ( LatestCY ) - 1, MONTH ( LatestCY ), DAY ( LatestCY ) ) )

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

Best Regards,
Herbert

[bebeto]

Thanks @v-haibl-msft

I already had a month column in my DimFecha dimension. I have done the calculation as you said, but the result of the average is incorrect:

AVGEmpleadosActivos = AVERAGEX ( SUMMARIZE ( ALL ( DimFecha ); DimFecha[Month]; "Active_Count"; Empleados[EmpleadosActivosPeriodo]); [Active_Count] )

And I add this measure in the "Line values" but it is not showed:

[v-haibl-msft]

@bebeto

I’d like to know the method of calculating the number of employees active you wanted. Could you please give some sample data and the expected output result? It may help us to determine the formula in FILTER() function.

Best Regards,

Herbert

[bebeto]

Thanks kdejonge, your idea seems to be ok, I have to check the results but it looks fine.

Vvlearde, I thinkyour proposal is incorrect. I understand your calculation will show only the employees whose "fecha incoporacion" is higher than MIN date in the filter and the "date_to" is lower than the MAX date in the filter. This means, it will only show employees thar have joined the company and have left the company in the filtered period... and this is not what I want.

Herbert_Liu, here you have an example of my data:

birth_date	Fecha_incorporacion	date_to	first_name	name	resource_id	resource_typ	sex
21/11/1984	10/11/2008	05/03/2016	ALBERTO	OJEDA RUIZ, ALBERTO	OJEDRUIZA	1	M
20/02/1989	12/03/2012	31/12/2099	IKER	LOPEZ PEREZ, IKER	LOPEPEREI	1	M
18/05/1984	12/03/2012	31/01/2016	CRISTINA	RAMOS GOMEZ, CRISTINA	RAMOGOMEC	1	F
27/01/1967	01/04/2012	30/04/2016	ROBERTO	ALBA SOLA, ROBEERTO	ALBASOLAR	1	M

And what I want to get is the acumulate per month in the actual year, and compare it with previous year. In this example, it must show (today is July 2016):

January 2016  --> 4 active employees

February 2016 --> 3 active employees (Alberto, Iker and Roberto)

March 2016 --> 2 active employees (Iker and Roberto)

April 2016 --> 1 active employee (Iker)

May 2016 --> 1 active employee (Iker)

June 2016 --> 1 active employee (Iker)

July 2016 --> 1 active employee (Iker)

And show that in a bar chart by month in the actual year.

[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

[bebeto]

Thanks Herbert!

This is that I wanted! 

One question, I mustn´t create a fisical relationship between employees table and DimFecha, must I? And if it doesn´t have to exist, what if I need it in other chart or query?

And last question, how can I filter the chart dinamically to show only the months from January to actual month of the current year?

[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]

Great! I am changing from multidimensional to tabular and these kind of things are new for me... Thanks a lot!

[kdejonge]

you could do something like this:

Measure = CALCULATE(COUNTROWS(Table1),
                                                                      FILTER(Table1
                                                                                       ,Table1[startdate] <= MIN('Table'[Date])
                                                                                      && Table1[enddate] > max('Table'[Date])
)
)

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.

Hope that helps,

Kasper