DAX - Dias Úteis Mês - Contar dentro do contexto e no mês posterior entender o que ficou de atestado

[Mateus_Silva]

Olá, Tudo bem?

Verifiquei uma resposta, mas não consegui aplicar ao meu modelo essa tratativa dos dados que estou trabalhando de RH de atestado.

 

Link no qual tentei fazer:

Regras em vermelho são as que não consegue tratar:

 

REGRA 1:

MAIOR DATA DE ATESTADO (DATA FINAL) MENOS A MENOR DATA

Medida = Quantidade de Dias (Já Existe) - OK

 

REGRA 2:

ONDE NAO TIVER MAIOR DATA(DATA FINAL), COLOCAR HOJE;

Não tem -> (Comparar com o valor Today()) - OK

 

REGRA 3:

REFERENCIA PRIMEIO DIA E ULTIMO DIA DO MES;

Não tem -> Quantos dias de atestado tem no mês(Contar e saber quantos foram utilizado em um mês no outro)

 

REGRA 4:

SEMPRE SOMAR MAIS 1 (PARA SABER EXATAMENTE QUANTOS DIAS SE PASSARAM)

Quantidade de Dias (Já Existe) - OK

 

REGRA 5:

MAXIMO A SER CONSIDERADO PARA ABSENTEISMO: 15 DIAS DE ATESTADO

Não tem (Setar valor no 15)

Desde já, agradeço.

Queria uma lógica nesse sentindo:

Teste Absteismo = DATEDIFF(

IF(fatestado[dtinicial] = dentro do mês, dtinicial, MIN(dCalendario[ID Data])),

IF(fatestado[dtfinal] = dentro do mês, dtfinal, max(dCalendario[ID Data]))

Modelo Atestado.pbix

[Bruno Abdalla de Souza]

@Mateus_Silva, veja se resolve a questão do absenteísmo, por favor.

Dias de atestado Absenteísmo = 
    VAR vDataMin = MIN(dCalendario[ID Data])
    VAR vDataMax = MAX(dCalendario[ID Data])
RETURN
    SUMX(
        CALCULATETABLE(
            fAtestado,
            REMOVEFILTERS(dCalendario)
        ),
            VAR vIniAtestado = 
                    fAtestado[DTINICIO]
            VAR vDataLimAbsenteismo = 
                vIniAtestado + 14
            VAR vFimAtestado = 
                    fAtestado[DTFINAL]
            VAR vFimAtestadoAbsenteismo = 
                IF(
                    vFimAtestado <= vDataLimAbsenteismo,
                    vFimAtestado,
                    vDataLimAbsenteismo
                )
            VAR vDataIni = 
                IF(
                    vDataMin > vIniAtestado,
                    vDataMin,
                    vIniAtestado
                )
            
            VAR vDataFim = 
                IF(
                    vDataMax < vFimAtestadoAbsenteismo,
                    vDataMax,
                    vFimAtestadoAbsenteismo
                )
        RETURN
            IF(
                vIniAtestado < vDataMax &&
                vFimAtestadoAbsenteismo > vDataMin,
                DATEDIFF(vDataIni, vDataFim, DAY) + 1
            )
    )

 

ModeloAtestado.pbix

[Bruno Abdalla de Souza]

@Mateus_Silva, depois diga se resolveu, por favor. Caso tenha resolvido, favor encerrar o tópico.

[Bruno Abdalla de Souza]

@Mateus_Silva, seguem minhas dúvidas:

Regra 3: supondo um atestado que se inicia em 15/10/2021 e termina em 10/11/2021. Qual o resultado esperado?

 

Regra 5: explique melhor o resultado desejado baseado em exemplos, por favor.

 

[Mateus_Silva]

@Bruno Abdalla de Souza,  de novo agradeço pela força.

Segue devolutiva:

Regra 3: supondo um atestado que se inicia em 15/10/2021 e termina em 10/11/2021. Qual o resultado esperado?

R - Seria quando colocasse outubro 21(Mas restringia por conta dos 15 dias) dias e para novembro 10 dias.

Regra 5: explique melhor o resultado desejado baseado em exemplos, por favor.

EXEMPLO:
ORIGINAL EXCEL:
 
DADOS DA CELULAS EM CALCULO - 1 PESSOA COM ATESTADO EM 07/09/2021 A 05/10/2021 - 29 DIAS DE ATESTADO
 
Data Final =  05/10/2021
Data Inicial = 07/09/2021
 
=SE((SE(K51<=$K$59;K51;$K$59)-(SE(J51>=$J$59;J51;$J$59)))>=14;14;(SE(K51<=$K$59;K51;$K$59)-(SE(J51>=$J$59;J51;$J$59))))+1
 
INTERPRETADA:
 
=SE((SE(05/10/2021 <=30/09/2021;05/10/2021;30/09/2021)-(SE(07/09/2021>= 01/09/2021;07/09/2021;01/09/2021)))>=14;14;(SE(05/10/2021<=30/09/2021;05/10/2021;30/09/2021)-(SE(07/09/2021>=01/09/2021;07/09/2021;01/09/2021))))+1

[Bruno Abdalla de Souza]

OK, vou tentar aqui depois.

 

Outra coisa, sua dCalendario começa em 1900!! Tem necessidade? O ideal seria ter somente os anos que estão sendo utilizados na tabela de atestados.

[Mateus_Silva]

9 minutos atrás, Bruno Abdalla de Souza disse:

OK, vou tentar aqui depois.

 

Outra coisa, sua dCalendario começa em 1900!! Tem necessidade? O ideal seria ter somente os anos que estão sendo utilizados na tabela de atestados.

Nesse caso da dCalendario a gente setou a data mínima da dPessoa[DTNASCIMENTO], por que, nesse modelo do RH tem outras Fatos ai para colocar como exemplo copiei e colei em um excel a Fato(Colocando somente um ano) e a dCalendario.
Mais muito obrigado pelas dicas.

[Bruno Abdalla de Souza]

Existem pessoas então que não tem cadastro de data de nascimento. Antes de pegar o valor mínimo, filtre os valores nulos e depois pegue o valor mínimo.

[Mateus_Silva]

51 minutos atrás, Bruno Abdalla de Souza disse:

Existem pessoas então que não tem cadastro de data de nascimento. Antes de pegar o valor mínimo, filtre os valores nulos e depois pegue o valor mínimo.

Isso mesmo apliquei aqui, muito obrigado pela dica.

[Bruno Abdalla de Souza]

@Mateus_Silva, veja se a regra 3 é isso:

 

Dias de atestado = 
    VAR vDataMin = CALCULATE(MIN(dCalendario[ID Data]))
    VAR vDataMax = CALCULATE(MAX(dCalendario[ID Data]))
RETURN
    SUMX(
        VALUES(fAtestado[CHAPA]),
            VAR vIniAtestado = CALCULATE(MAX(fAtestado[DTINICIO]))
            VAR vFimAtestado = CALCULATE(MAX(fAtestado[DTFINAL]))
            VAR vDataIni = 
                IF(
                    vDataMin > vIniAtestado,
                    vDataMin,
                    vIniAtestado
                )
            VAR vDataFim = 
                IF(
                    vDataMax < vFimAtestado,
                    vDataMax,
                    vFimAtestado
                )
        RETURN
            DATEDIFF(vDataIni, vDataFim, DAY)
    )

 

A regra 5 eu tento depois.

 

ModeloAtestado.pbix

[Bruno Abdalla de Souza]

Regra 5:

supondo um atestado que se inicia em 15/10/2021 e termina em 10/11/2021, ou seja, temos 16 dias em outubro e 10 dias em novembro, somando 26 dias.

A visão será mensal? Se sim, o que deve ser mostrado em outubro e novembro? Se não, será mostrada como, em um cartão?

Explique como vc quer que o resultado seja mostrado no Power BI e como chegou no resultado, por favor.

 

[Mateus_Silva]

17 minutos atrás, Bruno Abdalla de Souza disse:

Regra 5:

supondo um atestado que se inicia em 15/10/2021 e termina em 10/11/2021, ou seja, temos 16 dias em outubro e 10 dias em novembro, somando 26 dias.

A visão será mensal? Se sim, o que deve ser mostrado em outubro e novembro? Se não, será mostrada como, em um cartão?

Explique como vc quer que o resultado seja mostrado no Power BI e como chegou no resultado, por favor.

 

Nesse caso é pra mostrar 15 dias em outubro e 0 em novembro, pois pegaria a restrição da contagem de 15 dias.

[Bruno Abdalla de Souza]

Olhando sua tabela de fAtestados, acho melhor transformar as duas colunas de data em uma só, adicionando uma coluna para dizer se é data inicial ou final. A coluna de RECCREATEDON vai servir para encontrar a outra data, sabendo que ambas pertencem ao mesmo atestado.

Da maneira como está, o relacionamento com a dCalendario está na data inicial, mas só funciona quando a data inicial é dentro do mesmo mês. Quando não está no mesmo mês, é como se não existisse quando está no mês da data final. Até poderia tentar usar a função USERELATIONSHIP, mas acho que vai complicar. 

Ex: data inicial 28/01/2020 e data final de 03/02/2020 ==> quando a dCalendario está em Janeiro, encontra a data de 28/01/2020 e filtra. Quando a dCalendario está em Fevereiro, não encontra a data final, pois o relacionamento é com a data inicial e não coma  final. Não sei se ficou claro o problema. 

Anexe as bases de dados fictícias, por favor, para que possa realizar as alterações.

[Mateus_Silva]

Em 17/11/2021 em 18:23, Bruno Abdalla de Souza disse:

Olhando sua tabela de fAtestados, acho melhor transformar as duas colunas de data em uma só, adicionando uma coluna para dizer se é data inicial ou final. A coluna de RECCREATEDON vai servir para encontrar a outra data, sabendo que ambas pertencem ao mesmo atestado.

Da maneira como está, o relacionamento com a dCalendario está na data inicial, mas só funciona quando a data inicial é dentro do mesmo mês. Quando não está no mesmo mês, é como se não existisse quando está no mês da data final. Até poderia tentar usar a função USERELATIONSHIP, mas acho que vai complicar. 

Ex: data inicial 28/01/2020 e data final de 03/02/2020 ==> quando a dCalendario está em Janeiro, encontra a data de 28/01/2020 e filtra. Quando a dCalendario está em Fevereiro, não encontra a data final, pois o relacionamento é com a data inicial e não coma  final. Não sei se ficou claro o problema. 

Anexe as bases de dados fictícias, por favor, para que possa realizar as alterações.

Olá @Bruno Abdalla de Souza, tudo bem?

Coloquei na transformação a data unificada ai criei a coluna "CONDICAO_DATA" que irá fazer a distinção entre a Inicio e Fim.

Segue o modelo em anexo.

Att,

 

 

Modelo Atestado.pbix Modelo Atestado.xlsx

[Bruno Abdalla de Souza]

@Mateus_Silva, estava olhando seus dados. Removi as duplicadas da tabela fTaestados, pois tinham várias linhas duplicadas. Memso assim, olha um exemplo que encontrei:

Veja que tem um atestado começando em 11/02/2020, mas não tem data final. Você vai resolver isso ou tem algum regra de negócio para resolver quando ocorre algo assim?

 

[Bruno Abdalla de Souza]

Outra coisa, cada Atestado precisa ter um ID para identificar, que irá se repetir (data inicial e final). Mas ele será usado para identificar a data inicial/final.

[Mateus_Silva]

1 hora atrás, Bruno Abdalla de Souza disse:

@Mateus_Silva, estava olhando seus dados. Removi as duplicadas da tabela fTaestados, pois tinham várias linhas duplicadas. Memso assim, olha um exemplo que encontrei:

Veja que tem um atestado começando em 11/02/2020, mas não tem data final. Você vai resolver isso ou tem algum regra de negócio para resolver quando ocorre algo assim?

 

Nesse caso vou passar uma condição no Select assumindo o hoje na DTFINAL para valor Nulo(Para poder contabilizar os dias),  obrigado pela observação.

"CASE DTFINAL WHEN NULL THEN   GETDATE() 
 ELSE DTFINAL"

[Bruno Abdalla de Souza]

ou no SQL Server
 

DTFINAL = ISNULL(DTFINAL, GETDATE())

[Mateus_Silva]

2 horas atrás, Bruno Abdalla de Souza disse:

Outra coisa, cada Atestado precisa ter um ID para identificar, que irá se repetir (data inicial e final). Mas ele será usado para identificar a data inicial/final.

Olá, Bruno

Coloquei o campo IDATESTADO e passe a condição do SQL ... muito obrigado.

Segue o modelo em anexo.

 

Att,

 

Modelo Atestado.pbix Modelo Atestado.xlsx

[Frank Figueredo]

@Mateus_Silva Bom dia tudo bem?

Ainda não ficou claro para mim qual a regra que você quer aplicar, qual problema você quer resolver.

Já li tudo, mas ainda não está totalmente claro. Você poderia fazer um resumo conceitual do seu problema e o que você espera como resultado final?

Quando você fala de Regra, está falando de Medida?

Os filtros que vão ter na tela, são apenas esse do modelo de ano e mês? 

 

[Mateus_Silva]

Em 20/11/2021 em 10:57, Frank Figueredo disse:

@Mateus_Silva Bom dia tudo bem?

Ainda não ficou claro para mim qual a regra que você quer aplicar, qual problema você quer resolver.

Já li tudo, mas ainda não está totalmente claro. Você poderia fazer um resumo conceitual do seu problema e o que você espera como resultado final?

Quando você fala de Regra, está falando de Medida?

Os filtros que vão ter na tela, são apenas esse do modelo de ano e mês? 

 

Olá @Frank Figueredo, bom dia!
Tudo bem?

Desculpa a demora em responder fui conversar com o analista de negócio da área antes para certificar o caminho.

 

1 -Ainda não ficou claro para mim qual a regra que você quer aplicar, qual problema você quer resolver.

2 - Já li tudo, mas ainda não está totalmente claro. Você poderia fazer um resumo conceitual do seu problema e o que você espera como resultado final?

R 1 e 2 - Contar quantos dias de atestado o colaborador teve dentro do mês, com as regras que citei(Não sendo maior que 15 dias)  e independente do mês que começou ou terminou, com isso, teremos um item para o calculo do absenteísmo e outros indicadores da área.

3 - Quando você fala de Regra, está falando de Medida?

R 3 - Isso seria na medida para verificar o contexto da regra para contar os dias do atestado entre os meses.

Ex: Atestado abaixo:

DTINICIAL = 20/11/2021

DTFINAL =  08/11/2021

Resultado = 10 dias Mês de Novembro + 5 dias Mês de Dezembro(Indo até os 15 dias)

No filtro mês de Outubro = Resultado mostrar 10 dias

No filtro mês de Novembro = Resultado mostrar 5 dias

No filtro ano = Resultado mostrar 15 dias.

 

4 - Os filtros que vão ter na tela, são apenas esse do modelo de ano e mês? 

R 4 - Não, além desses teria outros filtros.

Ex: Contexto de Diretoria, Setor, Unidade,  CID e entre outros das Dimensões que possuí no modelo.

 

[Frank Figueredo]

Em 22/11/2021 em 08:49, Mateus_Silva disse:

DTINICIAL = 20/11/2021

DTFINAL =  08/11/2021

Resultado = 10 dias Mês de Novembro + 5 dias Mês de Dezembro(Indo até os 15 dias)

@Mateus_Silva a DTFINAL não era pra ser 08/12/2021, ao invés de 08/11/2021?

Considerando que seja Dezembro o correto, quando você diz:

Citar

 

No filtro mês de Outubro = Resultado mostrar 10 dias

No filtro mês de Novembro = Resultado mostrar 5 dias

 

Você quis Dizer Novembro e Dezembro nos filtros?

[Mateus_Silva]

11 horas atrás, Frank Figueredo disse:

@Mateus_Silva a DTFINAL não era pra ser 08/12/2021, ao invés de 08/11/2021?

Considerando que seja Dezembro o correto, quando você diz:

Você quis Dizer Novembro e Dezembro nos filtros?

@Mateus_Silva a DTFINAL não era pra ser 08/12/2021, ao invés de 08/11/2021?

Isso desculpe pelo o erro, mas é isso mesmo.

Você quis Dizer Novembro e Dezembro nos filtros?

Isso no caso se ele filtrasse o ano desse atestado ou os dois meses que ele pertence mostrar os 15 dias serviria para os cálculos de acumulados.

 

Att,

Mateus Silva.

 

•  

 

[Bruno Abdalla de Souza]

@Mateus_Silva, veja se esta medida resolva a questão 3.

Dias de atestado = 
    VAR vDataMin = MIN(dCalendario[ID Data])
    VAR vDataMax = MAX(dCalendario[ID Data])
RETURN
    SUMX(
        CALCULATETABLE(
            fAtestado,
            REMOVEFILTERS(dCalendario)
        ),
            VAR vIniAtestado = 
                    fAtestado[DTINICIO]
            VAR vFimAtestado = 
                    fAtestado[DTFINAL]
            VAR vDataIni = 
                IF(
                    vDataMin > vIniAtestado,
                    vDataMin,
                    vIniAtestado
                )
            VAR vDataFim = 
                IF(
                    vDataMax < vFimAtestado,
                    vDataMax,
                    vFimAtestado
                )
        RETURN
            IF(
                vIniAtestado < vDataMax &&
                vFimAtestado > vDataMin,
                DATEDIFF(vDataIni, vDataFim, DAY) + 1
            )
    )

 

ModeloAtestado.pbix

[Roseane]

@Mateus_Silva
 

Olá tudo bem?

Já resolveu ou ainda está com problemas?

 

[Mateus_Silva]

10 horas atrás, Roseane disse:

@Mateus_Silva
 

Olá tudo bem?

Já resolveu ou ainda está com problemas?

 

Olá Roseane, bom dia!
Tudo bem?

Ainda não estava ontem conversando com o Bruno, está faltando a gente realizar a regra dos 15 dias úteis para ele assumir um valor falamos até sobre setar uma nova data quando esse limite chegasse, mas não concluído ainda.

Qualquer dúvida, favor contatar.

 

Att,