Eu precisei montar esse cálculo aqui como que a gente vai resolver isso bom vamos fazer aqui do zero vou conectar vou pensar aqui com vocês pra gente chegar numa solução Bem bacana inclusive tá tem alguns milhões de métodos como eu sempre falo aqui dava para para utilizar Power query para algumas coisas eu vou fazer aqui um pouco de powerquery um pouco de dax pra gente chegar nesse resultado Tudo bem então vamos lá eu já abri aqui ó meu Power bi em branco eu vou ler esse arquivo Tudo bem então ó base horas isso aqui é bem tranquilo não é o foco da aula só tô lendo aqui para.
Fazer todo o caminho eu gosto de sempre ter uma aula completa tá então ó transformar dados tô puxando aqui os o arquivo tenho aqui data Inicial tenho data final então eu vou ó usar primeira linha como cabeçalho e vou também ã já já vou também tipar aqui né utilizando detectar tipo de dados mesmo beleza Ten o data Inicial Ten o data final como que eu vou fazer então para eu fazer esse cálculo tá por qu se eu venho aqui ó vou até chamar aqui de tb horas Ok ó tb horas fechar e aplicar se.
Eu jogo isso para o meu modelo de dados e monto um cálculo aqui tudo bem então vou vir até aqui ó em exibição de tabela nova coluna se eu venho e coloco ó horas de trabalho por exemplo e coloco aqui e dates date dif Faça a diferença entre a data Inicial e Opa coloquei primeira data final então data Inicial e a data final em horas né então eu já tenho isso pronto no power Bi eu vou ter o quê 50.
Horas só que é aquilo que eu falei se eu parar para pensar aqui não foram 50 horas de trabalho afinal pensando que uma pessoa começaria 8 9 horas da manhã até 5 6 horas né Depende da janela que você quiser considerar esse cálculo de 50 horas não foram 50 horas trabalhadas foram 50 horas né de da data inicial para data data final e como que eu faria aqui né para considerar horas úteis eu tô falando horas úteis por a gente pode fazer esse cálculo com dias e quando a gente faz com dias né eu tenho lá o.
Calendário me permitiria ali eu fazer esse cálculo de dias também porém as horas não tá presente numa de calendário concordam Como assim Ronan vou vir aqui vou criar uma uma nova tabela de de calendário Então o meu de calender vai ser um Calendar vindo da menor tabela da data Inicial tudo bem da minha tb horas data Inicial e da minha maior hora da minha TV horas data final pronto tá pronta a minha de calendário aqui tá ó Tá pronta a minha de calendário então se eu vim aqui ver os dados eu vou ter o.
Quê somente os dias sempre começando ali nas zer horas então eu não conseguiria também usar uma de calendário aqui imaginando uma de calendário para fazer esses cálculos Então como que eu faria bom eu vou montar uma tabela como uma de calendário Só que vai ser uma de calendário hora que eu vou montar nessa dimensão que eu vou montar galera o que que eu vou fazer eu vou colocar as horas tá então Perceba como eu só quero e fazer esse cálculo em horas eu vou montar somente com as horas por qu Ronan porque se eu montar com minutos né eu.
Vou ter lá e 60 minutos vezes 24 horas vezes x dias do meu de calendário Então vai montar uma tabela gigantesca né então como eu para esse projeto não quero calcular minutos né horas para mim já tá bom eu arredondo as horas e calculo para mim tá ok eu vou utilizar somente horas Ah mas eu preciso de minutos também eu preciso de segundos aí vai ficar uma tabela muito maior né Aí você teria que montar utilizando aí justamente essa hora e minutos Tudo bem eu vou fazer somente com as horas aqui para facilitar até o aprendizado aqui de.
Quem tá estudando e aprendendo aqui com a gente o dax beleza OK Ronan Como que você vai montar essa de calendário horora você vai usar alguma função você vai fazer via dax igual você fez aqui não Dessa vez tá galera até até daria tá para fazer via dax mas dessa vez eu vou fazer no power query tá então vou fazer no power query essa essa calendário hora que eu quero para fazer esse cálculo Tudo bem então vou voltar aqui ó transformar dados e eu já vou pedir aqui se você tá assistindo aqui deixa seu.
Like se inscreve aqui no canal se não for inscrito galera nos apoia aqui o canal compartilhe vire membro aqui se possível para nos apoiar com um cafezinho por mês nos ajude a manter esse canal Vivo e trazendo mais conteúdos de qualidade para vocês Beleza então conto com a sua força aí deixe seu like se inscreva Bom vamos lá como que eu vou montar essa de calendário hora que eu quero montar aqui tá E lembrando esse exercício que eu estou fazendo e essa resolução é uma que eu fiz não quer dizer que é melhor também não quer dizer que a que você deve fazer tudo bem Tô mostrando um exemplo você pode aprender.
VEJA MAIS AQUI:
- Aprenda Função DATA de Modo Prático e Rápido no Excel
- A Melhor Planilha para Controle de Investimentos
- Gráfico com fotos e imagens no Excel
Outros e usar o que achar melhor tá bom vamos lá vou começar aqui fazendo uma consulta nula mesmo e o que que eu vou
Trazer eu vou trazer a menor hora e a Mai maior hora daquela tabela que é o que eu preciso então ó menor hora vai ser igual ã list ponto minim da minha tb horas da coluna data hã Inicial tudo bem E vou até trazer aqui menor hora já para saber se deu certo Opa o nome da coluna deixa eu ver aqui ó data Inicial beleza volto aqui tem que ser igualzinho ó data inicial a.A menor hora eu já peguei agora eu vou pegar ó a maior é maior hora mas eu tô eu tô imaginando menor data hora né Vamos colocar correto aqui ó menor data hora e a maior data hora vai ser igual list.max a mesma coisa né tb horas e aqui vai ser data final aqui tem um espaços então eu vou dar um cont contrl C lá também na no nome da coluna então aqui ó vai ser data final show data final e data Inicial Então já ten a data Inicial e a data final que eu preciso para montar essa de calendário como que.
Eu vou fazer então essa lista de dias e horas eu vou utilizar aqui o próprio que você também já deve ter até utilizado deixa eu dar um zoom maior aqui o pró próprio lista de datas igual a list pdes vai montar para mim uma lista de datas começando numa data vai ser aqui a menor menor data hora no caso Então vou colocar aqui menor data hora o count eu teria que fazer o quê a diferença entre as duas para saber quantos dias eu vou precisar né vou colocar o número dois aqui só para testar ó já deu um erro.
Aqui porque faltou um parâmetro Por isso que eu gosto de ir testando tá então ó menor data hora dois e aqui tem mais um aqui ó indo de um em um de um em um dia né Beleza ó não conseguimos converter o valor porque você colocou uma data hora e só recebe data não tem problemas primeiro a gente vai vir aqui então converter ó essa menor data hora para date from e colocar aqui então eu vou pegar essa data hora certo ó data hora transformei em data Beleza então vou até colocar aqui ó transformei data hora em.
Data e aqui eu já vou dar um concluído só para ver se ele vai funcionar o duration de novo não funcionou porque o duration né tem que ser do tipo duration tipo de dado Então eu tenho que colocar assim ó duration e entrar que que é um duration né é justamente um tempo onde eu posso falar um dia um minuto 1 segundo então se eu colocar assim ó 1 eu tô falando que eu quero gerar uma lista de dois dias né de dois dias indo de um em um ok então se eu colocar concluído por.
Exemplo ó ele que que ele fez aqui ele pegou a menor data e gerou dois registros para mim indo de um em um Se eu colocar o número dois aqui para ficar claro vai ser dois dias então ele colocaria dois dias para mim na soma a cada item ó 25 27 eu poderia falar Me traga três linhas indo de dois em dois dias perceberam tá então aqui galera eu até conseguiria ã usar essa função de list P dates para gerar as horas que eu preciso tá o problema seria no count o.
Cálculo do count seria o o o o desafio aqui então por exemplo em vez de usar indo de um em um dia eu poderia colocar ó indo de um dia indo de uma em uma hora eu teria então aqui esses valores só que né ele gera para mim e coloca os dias porque eu tô usando list pdes então eu poderia trocar esse list pdes por list pdes and times então ele vai trazer para mim o dia e a hora e aí vai acontecer o.
Erro oposto do que tinha acontecido né eu não transformei data data hora em data agora ele tá falando ué É para usar uma data hora então eu meio eu meio que dei um erro corrigi para voltar para trás mas por que que eu fiz isso né galera não precisava por que que eu fiz porque você tá acostumado com list.dat você já montou uma D calendário no no power query por isso que eu eu causei o erro para mostrar e se você tentasse fazer dessa maneira também só trocando os parâmetros vai dar o erro por quê Porque no list pdes é uma lista de datas recebe uma data retorna datas agora a.
Gente tá usando list.dat times que recebe datas horas e retorna datas horas então ó isso aqui eu não preciso mais tá ó Então não preciso desse date from Afinal já está em data hora o meu tipo de dado tá então tipo de dado data hora então meu list pdes times recebe umas data hora me retorna datas e horas quando eu clicar em concluído eu vou ter agora ó começando da menor data indo de hora em hora Tudo bem então é exatamente isso.
Que a gente vai fazer só que é o que eu falei a contagem aqui que é o nosso desafio por quê Porque quantos registros
Eu quero bom eu quero a quantidade de horas entre a maior data e a menor data então eu vou fazer essa diferença de horas aqui sim eu não preciso considerar de horas úteis porque eu quero total de horas e aí eu vou ter a quantidade de registros que eu preciso tudo bem então por exemplo eu vou vir aqui ó por exemplo não né É Exatamente isso não é só um exemplo exatamente isso que a.Gente tem que fazer eu tenho que contar quantidade de horas entre as as datas né a menor e a maior quantas horas eu tenho entre a menor e a maior maior data hora Menos menor data hora vírgula eu vou pôr isso aqui pra gente ver o resultado Eu vou ter esse valor que não é exatamente a quantidade de horas tá vendo eu vou ter 4 dias 20 horas então eu preciso pegar o dia para eu ter isso numericamente contagem de quantas horas exatamente né tem algumas maneiras como.
Que eu vou fazer vou pegar o dia multiplicar por 24 pegar a hora e pronto para mim tá ótimo posso até somar um para ter uma hora a mais né considerando que pode arredondar então o que que eu vou fazer aqui eu vou pegar ó ã e vou pegar então primeiro dias desse quantidade de horas tá então ó dias vai ser igual a duration pegue os dias dessa data hora que eu tenho beleza então pega os dias isso vai dar 4ro né nesse caso aqui então eu já podia pegar o dias em.
Horas vai ser 4 xes 24 horas correto Além disso eu tenho que somar com o qu eu tenho que pegar as horas elas mesmas quantas horas vão ser vão ser 20 né como que eu vou pegar as horas horas da data da data hora vai ser igual a duration p Hours dessa quantidade de horas certo então ó isso aqui galera é 4 eu tô vou multiplicar por 24 isso aqui é esse 20 e aí eu posso somar um tá.
Considerando que pode ter uma uma hora mais uma hora menos arredondando ali tudo bem então dias em horas mais horas da dessa data vai dar o total de horas exatamente entre essa diferença então o que que eu tenho que fazer Opa dei enter aqui sem querer o que que eu tenho que fazer total de horas entre datas vai ser igual dias em horas que já teve a ali a multiplicação por 24 mais horas da data hora então o total de horas entre datas ó vamos ver quanto que vai dar aqui 117.
Tá então são 117 horas exatamente entre essa data tá e a maior data que é essa aqui tudo bem então se eu fizesse uma uma subtração de horas date um date dif aqui daria o esse total de horas basicamente que que eu vou fazer então agora eu vou aqui ó na lista de datas que a gente estava montando deixa eu até voltar aqui dar um zoom para facilitar para você ó eu vou fazer o seguinte não são mais três registros que eu quero lembra que tava três registros aqui eu vou querer na verdade quantos registros.
Eu vou querer o total de horas aqui ó 117 registros então ele vai ir montando ó até chegar exatamente ó no dia 30:0 8 que é exatamente a hora daqui tá vendo ele iniciou exatamente nessa menor hora e foi até a maior hora entenderam então a gente faz a diferença de horas para saber quantos registros de horas eu vou ter né quantas horas aí nessas datas eu vou ter para poder ter esse resultado o que eu poderia fazer até para para.
Garantir né vai que Putz não pegou a a primeira hora a última hora e tudo mais eu poderia colocar um registro a mais um registro a menos para garantir Tudo bem então se igual eu fiz eu somei um aqui para garantir mas podia até colocar mais mais dois aqui começar de uma hora antes tá então aqui no caso eu vou somar duas horas a mais além de todas só para para ter ali não tem problema nenhum e o que que eu preciso fazer agora converter isso né para uma tabela porque eu vou usar isso como uma dimensão certo e qual.
Que é o nosso problema aqui né que seriam os minutos então Ó vou chamar aqui de hor data hora mesmo data hora vou converter isso para data hora e o que que eu vou fazer eu transformar a hora e vou liar ou início ou fim tá quando eu colocar aqui ó time. start of hour ele vai gerar para mim né ã eu tô usando start Porque como começou meio-dia e 34 e eu prefiro que comece a minha D calendário de calendário hora aqui no meio-dia Porque daí eu vou ter do meio-dia mais aqueles dois registros que eu coloquei como aqui ó até mais.
Esses dois registros que eu coloquei a mais para garantir que tenha todas as horas que eu preciso tudo bem então aqui eu vou ter as 117 né horas 118 horas tá vendo 118 17 16 começando lá da hora inicial da minha base de dados tudo bem Ah eu tô montando aqui no meu Ronan tô seguindo o seu vídeo obviamente que você éa trocar aqui né a sua data Inicial né E a sua data final sua data hora Inicial e sua data hora final isso tem que ficar claro aqui para vocês Além disso tá galera vou criar uma coluna A mais.
Porque depois eu posso até ligar essa tabela a uma de calendário normal tá então eu vou criar aqui uma coluna Onde eu pego só a data da data hora então date from data hora certo porque daí depois eu posso utilizar isso com a calendário se eu quiser ó data então eu tenho aqui também as datas para eu poder me conectar se eu quiser a minha de calendário que eu tiver no meu modelo de D Opa Type dates dates beleza tá aqui ó data hora e data tudo isso que a gente fez foi para.
Ter no modelo essas datas horas para eu fazer cálculo de contagem de horas tá então lembrando se você quisesse usar o minuto O que que você teria que fazer tá que que você teria que fazer ou segundos Eu nem vou falar né porque seria mais longe ainda mas basicamente o seu duration não seria em horas seriam em minutos você teria que fazer hora vezes 20 dias vezes 24 horas vezes 60 minutos certo pegar as horas vezes 60 minutos e aí pegar o duration pon minutes Então.
Você teria mais uma etapa pegando duration pon minutes tudo bem e aí você teria essa tabela com data hora e minutos ali tudo bem Eu tô usando só hora aqui nesse caso de uso show temos aqui então a nossa tabela pronta já posso carregar ela pro meu modelo já vai funcionar qual que seria sua sua opinião sobre isso já funcionaria conseguiria utilizar bom pensa comigo tá Primeiro vou chamar aqui ó de data hora Qual que é o problema que eu tenho ainda aqui os meus minutos né então eu.
Fiz isso na minha na minha de calendário ali de data hora minha D data hora tudo bem minha dimensão de data hora como que eu vou conectar se tem minuto aqui então eu preciso também arredondar esses dois horários tá você poderia arredondar como achasse melhor ah a partir do da dos 30 eu jogo para para cima qual que é o ideal sempre arredondar tudo para baixo ou para cima porque daí o cálculo vai ficar mais exato então por exemplo apesar de tá 1234 e 1430 se eu jogo os.
Dois para baixo vai ficar 12 certo e 14 então o cálculo de horas vai bater ainda nesse arredondamento então eu tenho que jogar os dois para baixo ou os dois para cima para que o cálculo de hora faça sentido Tudo bem então sempre lembre disso joga os dois pro mesmo não faça tipo ah se for passou da meia hora joga para cima senão o cálculo de hora não vai bater tá vai vai dar hora errada aí vai ter muitos minutos que vão acabar arredondando errado então aqui no caso vou fazer a mesma coisa que eu fiz ali eu posso até criar uma coluna adicional.
Para não estragar o meu modelo e ter a hora exata então eu tenho a data Inicial exata a data final exata né Por que Ron que você vai fazer isso porque depois eu não tô eu não tô referenciando essa data aqui concorda aonde que eu faço isso ó Lembrando aqui ó eu não tô pegando aqui então não vou estragar a minha coluna original né eu vou pegar e vou criar duas colunas novas com a data Inicial e a data final corrigida para eu me conectar e fazer os cálculos então ó coluna personalizada vai ser data Inicial aí eu vou chamar de ahad de arredondamento só para para facilitar.
Aqui tá galera data Inicial tudo bem E vou fazer o mesmo aqui já vou colocar Type Type date time aqui vou fazer o mesmo né ã pra outra coluna aqui então vamos lá adicionar coluna vai ser data final ã beleza Type dates faltou renomear ali né então vou trocar aqui paraa data final a Red Show de bola que que falta fazer aqui vou transformar né ó transformar.
Hora início da hora transformar hora início da hora então agora eu tenho aqui as horas que vão bater com outro modelo lá tudo bem então agora sim galera eu consigo trazer isso tudo pro meu modelo ele vai trazer aqui pro meu modelo eu consigo conectar conseguiria conectar aqui com a minha ã a minha de Calendar de data e hora minha minha calendário de data e hora tudo bem Ó data hora então eu consigo ligar por exemplo a data hora com a data inicial a.
R ele vai conectar deu tudo certo e aí como que eu vou fazer esse cálculo agora né utilizando a data Inicial e a data final porque igual eu fiz uma conexão aqui aqui então eu vou posso fazer outra conexão posso mas né não fica exatamente como eu tô pensando Então como que eu vou fazer esse cálculo agora considerando só as horas úteis bom como agora eu tenho as horas fica muito mais fácil eu falar é conte quantas horas eu tenho entre uma data e outra né usando aqui essa dimensão esse relacionamento.
Mesmo eu nem precisava para montar o meu cálculo tá porque eu só precisava da tabela para fazer o cálculo em cima da tabela então tendo isso aqui ou excluindo isso aqui não vai fazer diferença para mim tá eu não precisaria do relacionamento eu precisava só da tabela porque eu vou realizar filtros ali né vou entrar com alguns cálculos Então vamos lá agora na tabela de tbh certo que é a nossa tabela essa hora de trabalho a gente sabe que tá errada é uma horas total agora eu vou montar uma nova coluna calculada que vai ser horas.
De trabalho úteis né Vamos chamar assim para ficar claro como que vou fazer esse cálculo eu quero contar quantas horas eu tenho entre a data Inicial e a data final então Vamos lá por que que eu falei que não preciso daquele relacionamento porque eu vou fazer aqui numa coluna calculada também né tem isso além da do tem esse ponto também mas porque eu só quero fazer os filtros na tabela considerando essas duas datas e trazer a contagem então por exemplo eu vou colocar aqui ó V DT in só para ajudar a gente a var data inicial vai.
Ser a própria data inicial a head Ok V DT fim vai ser igual a data final a r tudo bem Eu tenho a data Inicial Eu tenho a data final que que eu preciso fazer eu preciso montar um filtro Então vou colocar aqui ó var tb calculada tá posso fazer dessa maneira posso fazer de diversas outras maneiras o dax aqui é legal por isso minha tabela calculada vai ser o quê vai ser um Filter em toda Por que eu nem precisaria colocar o w aqui porque como eu disse eu tô fazendo uma coluna calculada já seria o w mas se.
Você for usar aí numa numa numa medida você precisaria colocar o a aqui para fazer esse fio Tudo bem então ó toda de data hora eu quero pegar toda a tabela e eu quero filtrar onde a data hora é maior ou igual a data Inicial e a data hora a data hora é menor igual a data final esse filtro aqui vai me trazer exatamente quantas linhas de horas eu tenho ok Calma a gente não coloc as.
Horas úteis ainda qual vai ser meu resultado aqui ó o meu resultado né o o o return aqui vai ser igual a count rows quantas linhas eu tenho nesse nessa tabela filtrada Tudo bem então esse resultado que eu vou dar agora enter aqui galera vocês vão perceber o seguinte ele deu bem próximo né das horas de trabalho ele deu uma a mais justamente por causa do filtro correto o correto aqui seria colocar esse count.
Rolls menos um Então eu tenho que colocar aqui o menos um vocês vão ver que vai bater exatamente com as horas de trabalho que é por mim até agora nada de errado que que eu fiz aqui galera eu reproduzi uma contagem entre datas e horas né data hora normal igual um calculate date dif ali só que agora com esse filtro eu consigo implementar né ignorando horas que sejam maior ou menor do que as horas que eu desejar que considerar horas.
Úteis tá duas maneiras de fazer isso tá criar se é uma hora útil ou não na minha d na minha D calendário hora ou colocar no próprio filtro Como assim Ronan ou eu venho aqui e falo o seguinte se for uma hora maior do que 9 horas considerar se for uma hora menor que 9 horas não contar Então essa é uma maneira e não tá errado eu acho que até é bacana fazer assim vamos lá vamos testar assim ó então além de est entre essas duas horas eu quero fazer o seguinte se.
à a data se a hora ó hour da minha data hora for tem que ser né maior do que 9 tudo bem considerar então só as horas é maior ou igual a 9 então eu quero considerar 9 horas da manhã como o início ali das minhas horas úteis e a hora da minha D data hora data hora tem que ser menor ou igual a 18 Então desse jeito que eu tô.
Fazendo aqui galera que qual que é o ponto desse jeito que eu tô fazendo aqui eu tô eu tô meio que falando que as minhas horas úteis vão estar nessa fórmula então se eu der um enter aqui ó ele vai calcular corretamente para mim já funciona tá pronto isso aqui tá então por exemplo quantas horas úteis considerando né que o que o dia começa entre as 9 e termina as 18 certo quantas horas úteis eu vou ter entre essas duas datas eu vou ter 22 horas então se eu fizer esse cálculo aqui eu vou ver que realmente eu vou ter 22 horas entre esse.
Dia e esse dia de horas tudo bem porém fica fixo aqui na fórmula fica fixo aqui na fórmula então o que que eu posso fazer que é legal também fazer eu montar esse cálculo aqui na minha própria dimensão então eu poderia fazer essa esse cálculo aqui ou lá no power query também o que você achar melhor porque eu posso deixar como parâmetro até pro usuário escolher depois na hora que for usar o dashboard tá então até isso eu conseguiria fazer montando aqui com parâmetros tudo bem Então qual que é o legal eu venho aqui ó nova coluna e eu.
CLique no Link do Vídeo: https://www.youtube.com/watch?v=4DmB3FdRk7c
Postar um comentário