Office Attendance Adherence Calculation

[amaral_diego]

In Power BI I have a table called "f_PRINCIPAL", I need to create a DAX code for adherence with the columns "SEMANA" and "POLÍTICA" following the rules below:

First step: Count in the week columns "CONTADOR" that have the options "1" which indicates that the person came to the office and "0" which indicates that the person did not come, according to the column "SEMANA" which in turn has the options "Semana 36, Semana 37, etc., the number of days the person was in the office.

Second step: Identify the number of possible days. To calculate, we take the number of days in the week (the standard number of days in the week is 5) and subtract the days of vacation, leave, and absences (Do not consider absences). Column "MOTIVO".

Third step: Divide the first step by the second step.

Fourth step: Identify the person's policy (Column "POLÍTICA").

Fifth step: If the person's policy is 3 days and the division value is greater than 0.6, it means that the policy was met "Cumprida". If the person's policy is 4 days and the division value is greater than 0.8, it means that the policy was met "Cumprida". If the possible days are zero, the division will always be by zero, so consider the policy met. (For example, if the person is on vacation, they will not have any days).

I've tried with this code below

StatusAderência =

VAR SemanaAtual = 'f_PRINCIPAL'[SEMANA]

VAR SiglaAtual = 'f_PRINCIPAL'[SIGLA]

VAR MotivoAtual = 'f_PRINCIPAL'[MOTIVO]

VAR PoliticaAtual = 'f_PRINCIPAL'[POLÍTICA]

VAR DiasNoEscritorio =

    CALCULATE(

        COUNTROWS('f_PRINCIPAL'),

        FILTER(

            'f_PRINCIPAL',

            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&

            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&

            'f_PRINCIPAL'[CONTADOR] = 1

        )

    )

VAR DiasSemana = 5

VAR DiasAusentes =

    CALCULATE(

        COUNTROWS('f_PRINCIPAL'),

        FILTER(

            'f_PRINCIPAL',

            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&

            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&

            'f_PRINCIPAL'[MOTIVO] IN {"Férias", "Afastamento", "Ausência"}

        )

    )

VAR DiasPossiveis = DiasSemana - DiasAusentes

VAR Aderencia = DIVIDE(DiasNoEscritorio, DiasPossiveis, 0)

RETURN

IF(

    DiasPossiveis = 0,

    "Cumprida",

    SWITCH(

        TRUE(),

        PoliticaAtual = "3" && Aderencia > 0.6, "Cumprida",

        PoliticaAtual = "4" && Aderencia > 0.8, "Cumprida",

        "Não Cumprida"

    )

)

Although, the customer found the compliance percentages to be very low for all levels, and indicated that we will use:
"Number of days of presence / Number of possible days of going to the office"

Cold assist me to apply this on this code?

[Anonymous]

Hi @amaral_diego ,

You can try the following code.

StatusAderencia =
VAR SemanaAtual = 'f_PRINCIPAL'[SEMANA]
VAR SiglaAtual = 'f_PRINCIPAL'[SIGLA]
VAR PoliticaAtual = 'f_PRINCIPAL'[POLÍTICA]
-- First step: Count the days a person was in the office in the current week
VAR DiasNoEscritorio =
    CALCULATE(
        COUNTROWS('f_PRINCIPAL'),
        FILTER(
            'f_PRINCIPAL',
            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&
            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&
            'f_PRINCIPAL'[CONTADOR] = 1 &&
            'f_PRINCIPAL'[MOTIVO] = "Presença"
        )
    )

Make sure that the “CONTADOR” and “MOTIVO” columns are filtered correctly, and make sure that the values in the “POLÍTICA” column are correctly interpreted as “3” or “4” .

If that didn't solve your problem. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[amaral_diego]

I'm also using this another code to show on the matrix visual

AderenciaPolitica =
VAR TotalCumpriu =
COUNTROWS(
FILTER(
f_PRINCIPAL,
f_PRINCIPAL[StatusAderência] = "Cumprida"
)
)
VAR TotalRegistros = COUNTROWS(f_PRINCIPAL)
VAR PercentualCumpriu = DIVIDE(TotalCumpriu, TotalRegistros, 0)
RETURN
COALESCE(PercentualCumpriu, 0)

[amaral_diego]

He also mentioned, In the second step I understand it to be correct, in the first step it needs to be the week count with the reason "Presença"

so I made this code below

StatusAderencia =

VAR SemanaAtual = 'f_PRINCIPAL'[SEMANA]

VAR SiglaAtual = 'f_PRINCIPAL'[SIGLA]

VAR MotivoAtual = 'f_PRINCIPAL'[MOTIVO]

VAR PoliticaAtual = 'f_PRINCIPAL'[POLÍTICA]

-- First step: Count the days a person was in the office in the current week

VAR DiasNoEscritorio =

    CALCULATE(

        COUNTROWS('f_PRINCIPAL'),

        FILTER(

            'f_PRINCIPAL',

            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&

            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&

            'f_PRINCIPAL'[CONTADOR] = 1 &&

            'f_PRINCIPAL'[MOTIVO] = "Presença"

        )

    )

-- Second step: Calculate the possible days of going to the office

VAR DiasSemana = 5

VAR DiasAusentes =

    CALCULATE(

        COUNTROWS('f_PRINCIPAL'),

        FILTER(

            'f_PRINCIPAL',

            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&

            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&

            'f_PRINCIPAL'[MOTIVO] IN {"Férias", "Afastamento", "Ausência"}

        )

    )

VAR DiasPossiveis = DiasSemana - DiasAusentes

-- Third step: Calculate adherence (days of presence / possible days of going to the office)

VAR Aderencia = DIVIDE(DiasNoEscritorio, DiasPossiveis, 0)

-- Steps Four and Five: Verify that the Policy has been Followed

RETURN

IF(

    DiasPossiveis = 0,

    "Cumprida",

    SWITCH(

        TRUE(),

        PoliticaAtual = "3" && Aderencia > 0.6, "Cumprida",

        PoliticaAtual = "4" && Aderencia > 0.8, "Cumprida",

        "Não Cumprida"

    )

)

but the percentages still to be very low for all levels, it's show me the same numbers