Sum active employees by team and shift does not match

[RTERCERO]

Hello,

I have rotating shifts and im trying to sum active employees by team and shift.

We have 4 teams running as followed:

SG = Monday To Friday on Shift A

G1= rotating 5 Days on a day shift, 2 rest and 5 days at nigth

G2= rotating 5 Days on a day shift, 2 rest and 5 days at nigth

G3= rotating 5 Days on a day shift, 2 rest and 5 days at nigth

I have a calendar with the team and shift dates:

I also have the Employee history ( actives and non actives)

Im currently calculating Actives per day with the following formula:

Activos X Fecha = 

      CALCULATE(COUNTROWS(
    CALCULATETABLE('maestro_empleados (Ausentismo)',
    FILTER('maestro_empleados (Ausentismo)',
    'maestro_empleados (Ausentismo)'[FECHA INGRESO]<=MAX('Calendario de Grupos'[Fecha])
    && 'maestro_empleados (Ausentismo)'[ESTADO]="A"))))
+
    CALCULATE(COUNTROWS(
    CALCULATETABLE('maestro_empleados (Ausentismo)',
    FILTER('maestro_empleados (Ausentismo)',
    'maestro_empleados (Ausentismo)'[FECHA INGRESO]<=MAX('Calendario de Grupos'[Fecha])
    &&'maestro_empleados (Ausentismo)'[FECHA RETIRO]<>BLANK()
    &&'maestro_empleados (Ausentismo)'[FECHA RETIRO]>=MIN('Calendario de Grupos'[Fecha])
    && 'maestro_empleados (Ausentismo)'[ESTADO]="R"))))

For visuals works ok but when im trying to calculate active employees per day acording to the shift without it does not:

Items in the red square should match total:

Data Sample:  https://we.tl/t-EauMS0CqCX

Apreciate the help.

RT

[vanessafvg]

are you able to share your data model?  how have you linked the 2 tables of data together?  I think the issue might be some kind of overlap.  firstly you will need to join your dates to a mutual date table - what date do you have plotted on your graphs?  What is the relationship between these tables

[RTERCERO]

Hello @vanessafvg,

I was able to find the proper relationship, however It did affect another calculation, the problem was moved to another measure.

To give a better perspective of what I am trying to do, is calculate % of Absenteeism, formula is the following:

% Absenteeism = Hours of absenteeism / Man Hours in a Workday

This needs to be done by shift as I explained earlier, now I have Man Hours in a Workday figured out, but the hours of absenteeism do not match this is what im getting:

This is what it should be (page 2 of attached model):

Attaching model:

https://we.tl/t-GpGfy10qGf

Best Regards

RT

[vanessafvg · Accepted solution]

Hi Again

Its a little hard for me to understand  because of the spanish but once again I think this is date related.

Can you explain the purpose of the tables?  I feel like the model needs some adjustment but without understanding the purpose of what is in each table its hard to know what must move where.  What are these being used for maestro_empleados (Ausentismo) and Ausentismo Manual and  and how are they different.  What dates do you need from your model?

All the dates should link to one calendar date table.   A Calendar date table keeps the dates continious and is best practice to use one.   You created a calendario table, but you only linked one date to it.  You seem to need to use Fecha de Ausencia too but this is not linked.   You also have  bi directional relationship between Calendario de Grupos and maestro_empleados (Ausentismo) that can also be creating issues.  I think your model needs to be adjusted.   All "transational  / event type data should link to the date table.  It looks like  Ausentismo Manual and maestro_empleados (Ausentismo) both need to link to one dedicated date table to look at dates across the data 

It also looks like the calendar group table serves as a dimension and you should also be linking that to both the table rather than a bi direction relationship link them seperately.   Why do you link it via Calendario de Grupos

not sure if this makes sense.   as i said a little difficult to understand everything in the model as I am not Spanish.  

[RTERCERO]

@vanessafvg Thank you for the fast reply,

Tables explanation:

"Masestro_empleados" is the list of all employees active and inactive with the date of hiring and retired date, I use this to calculate how many active we have each day, the formulas is Active per date:

Activos X Fecha = 

      CALCULATE(COUNTROWS(
    CALCULATETABLE('maestro_empleados (Ausentismo)',
    FILTER('maestro_empleados (Ausentismo)',
    'maestro_empleados (Ausentismo)'[FECHA INGRESO]<=MAX('Calendario de Grupos'[Fecha])
    && 'maestro_empleados (Ausentismo)'[ESTADO]="A"))))
+
    CALCULATE(COUNTROWS(
    CALCULATETABLE('maestro_empleados (Ausentismo)',
    FILTER('maestro_empleados (Ausentismo)',
    'maestro_empleados (Ausentismo)'[FECHA INGRESO]<=MAX('Calendario de Grupos'[Fecha])
    &&'maestro_empleados (Ausentismo)'[FECHA RETIRO]<>BLANK()
    &&'maestro_empleados (Ausentismo)'[FECHA RETIRO]>=MIN('Calendario de Grupos'[Fecha])
    && 'maestro_empleados (Ausentismo)'[ESTADO]="R"))))

"Calendario Grupos" its the team and shift information for each date, This is not my main calendar, there are other tables in the complete model that have a relation to the actual man calendar which is: "Calendario"

"Ausentismo Manual" is the absenteeism registry, who was absent how mane hours and when

"UNIDADES" is a table to group business units according to leader

"Grupo Texto" is a table with the team names that i used to remove the many to many relationship Between "Calendario" and "Maestro_empleados"

Related Tables:

I reviewed this section and got the calculations to match in separate graphs, (see red rectangle), and when i place then in the same table individual values add perfectly but totals just in  "Horas Jornada" which is man hours per shift do not (see yellow rectangle should be "129,720"):

This is the formula I use for "Horas Jornada" :

Horas de Jornada = 
    COUNTROWS('Calendario de Grupos')*
    CALCULATE([Activos X Fecha],FIRSTNONBLANK('Calendario de Grupos'[Grupo],'Calendario de Grupos'[Grupo]))
    *12

Also when I try to filter by Business unit Absent hours disappear (red rectangles):

I'm sorry if it is a bit confusing but I'm trying to explain as the best I can. (wrote this post 2 times due changes I made to the model 😅)

I'm attaching the model with the new relations so you can review: 
https://we.tl/t-jcK1Uv490D

Thanks for the follow up