[Power BI] Userelationship - Como trabalhar com data de faturamento e data de vencimento na tabela FATO

Quando descobri a função USERELATIONSHIP no Power BI foi algo surreal para mim.
Fiquei pensando por horas, como isso é possível?

Eu precisava fazer uma relação entre os pedidos faturados e entregues dentro de cada mês, portanto a tabela fato possuía duas colunas com data, a do faturamento e a da entrega.

Fiquei testando e me questionando como poderia fazer essa análise em precisar adicionar uma nova tabela calendário, foi então que perguntei para o Rafael Mendonça e prontamente ele respondeu: “Utiliza a função USERELATIONSHIP”.

Pesquisei sobre a função e mesmo assim não consegui entender como trabalhar com ela.
Fiz alguns testes e depois de muito tentar, questionei o Rafael novamente e ele me ajudou a montar a medida.

Para exemplificar, criei uma tabela FATO com duas colunas de datas (data_fatura e data_vencimento), além do valor, conforme imagem abaixo:

Script para criar a tabela f_vendas no Power Query:

let
  Fonte = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "dZxZct06DET3km9XmeAkci2pt/9tPMkRgNO68udN0RwwNhpQ/v79Y8d3ad+1VPvz9cfmd5nXj3r+aP2r/vnv6++fMu5/vZbUc8kR6+v4Wj9rWvm2Ev+8vs+/ubexc59/a2rDn9b6Xbb/mO2r+zbY/fi26j92+Wo/S67Nc5f9XZb/GHbvci3p/q/n4rzMucuON1kctG8Z/NzXDl+z+KZ6/8GPaMxF0+7NryVy+T19lwMn1Y4nHcOXGJY0/jjl++8g67jLKbhUk9X6Zf/kOyFS+QOr+96nUnrn1XPNeZvDl+Av+y3Kn6PK8TX+XadCwpXi3se9zSlUM9w4331K7wgBG0+qIeD6Nf9dxvCH16mhp9F8l43jz/0gg5aCSZEWGnzzc+Z9ui8J+c4Qb+Mfqlz6rclTcDCrApOxfq+YsNhry3jz7n6XwTcU0fW411xWsvjoOKjO290uEw/bFBOv05UIm2r02br80ZXeVnDdse6rWOM2Ri1291mD6K57peiqa/p8ZW5T6bRt+6MLjKFMnDTj0YwxlwnGlqceX8LZpcf4sUo4QB5TIf/wkfNBMO5F8ZuL5XhEmFS0RzuV3MISa+1WwPXmPIlWV0P8MCgJAWXf8j//KDcJb7g2caukmsVEt0dDWXK6JazSwls79bxwl958m6YBKN9zzFssV8ScOIlxbN0vklxy7ZmOZvdJkhtEp0fPdAOba7hMm59XMerZWnFbaNi60lxqRJeC6CKKHiGX8TDctB0rngREMItJq/qayy1SBRtutJoH7wLFXJ4Wr57dncgkiqVdngFoRM6K+zZGutnhAR3CQyA3F94lmUzVxhg075Mqc815ldxmRFajfC/BZGjs3U8SNfGk/uJIhRCmL/ciyI2JqS/36MknH5DKsV36BiVe0SVfXNyPrsicTi+xcCIbpadTKtaGu33DUc3ESQ7fh6HBmASO5ZKrkjGxfgdEZCoxCQ4evI2BrElWM0eRlaFZxH3FmBW6TpcceNRqgFySQDMjHZ5tBB7x2QNvwmXo+yc62SGZNBmCZ+vTw8N6xEQ4U3cl2AOE55pegFAScvHgasgnaeLEFtXffbl73pjhdx1Azw2Pyi2XefTtDOKHGF/zTDwfsRXJrbtrH+pyuD3ts0A0PGoip4iI0+hLeG4G1y7VSXG76pQv11RPo7aJqCoD5+GSYXlUBKT1/YaMpa6wioBGASPrOwKsYp6HVGWesyvRjWBwK5YwUV03b7MRpGFtAkjLrafCyuaKfKHuIxJlkSJA8OaRrgsjmA+kOCLIZhocEOYoblhUuB7ltmdLoznwWeRSVhaS48yjeeHxYjV2JoW08gxHUwJPrJkPAJbeYis1DrjIkN8jZkk1LxG3f2L+StWf0Cpxk+QwQA6E+/dXr5b2AMhD89xhD0OLSNjPQrLsVFL8iIOMQCQM/ieSe5iWTCKYyPqRsVOiHuMFHBf+vFgk7nsbQSIKgM7iuYb9HjwqKZcdBjOxIGW9wt006MJPRnAp9khxaVLFEkZT6rhKd3IifNnzbQL2KA1IAFiVIDk9/Ru3EceqyIKZ2Rlzjnj2fNS0QDSOjArfXbmm7Vf6QtBu62/FpkA5a06DlE3cyTC9Vpa1iipjSYLGJ+qJk7ahgMgLD0mm4+vNtadaZ2wDvxXW6yzQagD7cIRGsLVagggUyA3b9Kjm2wOCJax0w6oFuqxUbO/AV+9c3yKgkUifair7LVwJBNueBa2py1HbO0sr5CYqIQuRxlpFOCKLQmTQIqju5SYsPlcpvRPrhZWLJwCUVz+ItFchvZZRz9504DEiqF0NsKz0mvvBL5LZDl8Lw0KpAow8vEpWGYQZSTxKeSsyKEHwrAfbhNu74wrzKIjmBNzhT1phJVUahT/kO+AGh5faV4aVovMTQYh4pey0npxth5Le606cI0WGJanSoEXGAeAUSbYh2xlw58l3p4DWK6l74Mfht1XcUSUzVX9R/5XMPo1hRaQ37APBOG9+BQ3hidLsamIm7MI6b3ryeu5CRsr7EsLb24MQieigmYllfWYmiUOxZDbAB6nYk9f1KqSRhhbP20EJSjnJUBxwpzSm7C5JZ4anZSgzQetBlR6P2JBeMt48rUrtELUkAByT6I4A9IIu7oq/xSELxoLc1zyoGtG8SRawglQsITP3KZGu2wMzhfF0N5dL/fmkISVpil9MN34cIzkBAnjaXEDsXNAKFSRFeNYMVag/15D4vUhgO4dhXVkilA07gY7w3XlS78n9/hIaRgQYKqkwTcwIu7yhkT+70vB+UUD/NDolmrpUvV6KVqYaW1orJa1YqYIF0WUWzrgkTQtnU6J/cx8klfwLx0E1Snc1cwC7TCdMb/EgJj20ThkM8Ye47XIGX+JlFZPqbnZSnkiXo4Xonk1ahL0DZXynluLctROlw6a60ObeihTitgpTctYn7bM+ebisO4F0a1h7LCe0PrrpeZtRIphlQiJFFleR0Cy9y4tUjMALJUjTJzry60H0k4man0skZ11UXyYtoQPQmaxoOhDmpIBnzaYa07Pkf3CynfIFWPIWoPTmZKLhGMlfSqkEjBImLMUoK/bmma3Yo0LMgxyhSHVSNf4O0IHiCKSH/C5qVWEyQcALOyxEWwIzMSs65RHOtJVIJTHIIC4GkTYzkc8zW7DcOQyBk3wXLuPNACMhKyyIlZXMr4KLBAahSOpO24ATgykoRxjeOmr0NMf9qA9zjCNNhkAufZL5QhDrLpkMYJtGvMTW8qIKULVFr9y0OGHzLUwzsVuD9W6eI6VJJuPAS1KZoMLciVa1AspsXRDM8jlFssXIjKLTAdwmMoGM64A1q1nla6sW2G2H2BiD4JkBEqsmN+ZLgosEIOQBAulIxpFQsqNJJbmNky2rffqiKOiILvhztggYLYYzwObQtDfpdBaPzBMdk0UV7xGqNvsIaU+EXNV9qI5HTZ2M2kz5S4EJXNY6xrukWYa4u5BupKiD2QUtvx9nAUi6ZfYHsQFz4OjQgCIZV7+S82WLipQkWz6S0XO8IqocgvlmwvD5jQs1pbxstfBIQYHxY3gLQFxfAv7oMD3mJM7YBS28f+MJL99/mwEj4N8DBW8qQXu7A7wbKy7qe2I2SOwGs2QrEIggr88CXavv9ohn0ZmD0wnXVT1HSpiXFn+mfXsUtTmHBPQL22OMSNJhKXskB1m4bt5FJmUsw7hMKrHHCesUo4kMmSDw+K3fe7jp6ZSAtC97yWikkClbtTMzpIkbSOsuu0KZJI+3cCTM9QMCRN4ZyqSgNgOFCswkAKN4n1vYeGPenxsNQMlwqfqA64XzHELfN0PjeODdDFgxdyntSKkDY1aXniB9xcgJKhoZZGTdlX4oAT/mj2WCQvrux34F/f1BCiRqYoWcf7APUKDH67Mvzw3qQHmZBE6R24V+0CEA+3rjbMV1Y8aqP5I3euogoXEUIdA6EGo+x2z/xWS0NqQqTUs0j9QyFGbkA2tDg3TiMhjoLUnrAhnJXGqLnGtyF+C49TosJ54jKYHjfVBagWFl/c2QUQOx6DQHZndapO1NNWa33PuaCp4km+aIyzvpe9Z3OZ7GISL6rCN6nakiYE++Vm7LOa21MJ1C6gy9p4K5YZlKZYswCfOMeCbVBQDP5qsxcr3RuRNQGSdlj1AqA9ZCgYkEJttQaJpqBOtGPsUTaSH3UZRKTZpUG/dAr0jqKRjpNuT8mj3anmlTFdMKef4Sh+2Y0pDQGj8aNSnkTrjmrFntKOOCRsFCjS1+n7VMj6uwJ8+pVHDmqWoZ763g8aR+AGgZaKCIPwKXjiRu9LuDDL2W02C/QIxocWsBKfTPfttFO4TNcgpGamgd43oZyZNcEW17E3HI3JR/tCF5/zFn5WmfSUnHUGpFkH/3/hgO1tEJdnUtZukejWe2AFkvvo/KDOdcosXuICQbmDkeJB2dOHRG5bofbAq5hfEZoSvZ4RhwkWLnfdBDO+MCA/sAiSSkYg4qJ+Ulo22ofnmQDAckzXS8ceIKUQp4MRkgwnD2kZUiltBPLOfWWIVWgpEeAPmZ8xP8VowySrUO4dVsOyjOBsk8MFL2CS9+DAKZSZ0/80V8OiOtrP3WsqnKBspcSk4HpMmwNWrBd0sTVoaQc/qKBISsWQMjBMYlGF3xWCWOIp/qdQ7AcnSdYxYN4yLy4VFm0ooa770PdUTv7ffvLmLASNvtWbxFW01GHjgtazHKpFMDrCujpxPm6M6UgilAXsJUI1j7pz7C/NKWJ783fGddZge78z4MbZYfbAklC4ds6OBJ4YsKJt4z3yVXMASmzdxM6Q2kAYtIzOE7TydVr4zTWSkgFgSBgOWcbxSGfD4R09s62iEsR0XxPPimuNpuWe3rHBNKO9e1fFxgMtFwIKHg+4c3lP0xvpUuEENgZGV01NEAUzTpSD8hS3mZ0WjQZdZt758SH9Ei2Y+UAtOLaVJp3muPz9KytMGfEWQleS7T5viBLwH1S9c8yS/c7JFr86CC+TijEnLeFMNvmphyWteQAcVocl7aMgNKdY0XxVdZQ92W4MyDlZQChZB6ZHmXIZyQdMcXy4+hBhJpE/OdMvkKKjqg+MZdGini7clYZoxMTYZTUzJMk9vEd3ys7fUDEM9b+hkEViwYQ4ZWto92cg7pJE0xYH5TILOzsX5PjIm9U2IYIv215GokqubrNjH1UIbW12w6MMYI82uUf/tUo4S/+ARYvsoTwGSlgcXvPAo6qNin4SgOc7wQP0XIod7eWB01qhL4QqouSU0jJ4LlEzFAg5bxF9BA2s/j9TI0vUlCUaIDpIdxBKBJlgAtvgdqv/XCRxTQ5SGZfDUpPPksJgcQS9wkiyyKcbG1nG5Amn/s5MT0v5RAFTPzywMdwchxJXxTKFystn2yiuT0D1NO0N3zkfjzqP2G+B8fOMV1CLOr8r6RJZ99X+EE1ieGlob49kkNHQGQEXF7m1/TeZbqn+nEp48e1HKYEf9jwS//+0eMltT+yJL8PxgC6OF8GUbvP999//c/", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [data_fatura = _t, data_vencimento = _t, valor = _t]
  ), 
  #"Tipo Alterado" = Table.TransformColumnTypes(
    Fonte, 
    {{"data_fatura", type date}, {"data_vencimento", type date}, {"valor", type number}}
  )
in
  #"Tipo Alterado"

Copie este código, abra o Power Query, crie uma consulta nula e vá no editor avançado para substituir o código por este.

Na sequência criei a tabela dimensão calendário:

Script para criar a tabela d_calendario no Power Query:

let
  DataMin = List.Min(f_vendas[data_fatura]), 
  DataMax = List.Max(f_vendas[data_vencimento]), 
  QtdeDias = Duration.Days(DataMax - DataMin) + 1, 
  AnoMin = Date.Year(DataMin), 
  ListaDatas = List.Dates(DataMin, QtdeDias, #duration(1, 0, 0, 0)), 
  Tabela = Table.FromList(ListaDatas, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
  Tipo_Data = Table.TransformColumnTypes(Tabela, {{"Column1", type date}}), 
  #"Colunas Renomeadas" = Table.RenameColumns(Tipo_Data, {{"Column1", "Data"}}), 
  Ano = Table.AddColumn(#"Colunas Renomeadas", "ano", each Date.Year([Data]), Int64.Type), 
  Mes = Table.AddColumn(Ano, "mes", each Date.Month([Data]), Int64.Type), 
  Descricao_Mes = Table.AddColumn(
    Mes, 
    "nome_mes", 
    each Text.Proper(Text.Start(Date.MonthName([Data]), 3)), 
    type text
  ), 
  Descricao_Mes_Ano = Table.AddColumn(
    Descricao_Mes, 
    "mes_ano", 
    each [nome_mes] & "/" & Text.From([ano])
  ), 
  Tipo_Mes_Ano = Table.TransformColumnTypes(Descricao_Mes_Ano, {{"mes_ano", type text}}), 
  Indice_Mes_Ano = Table.AddColumn(
    Tipo_Mes_Ano, 
    "indice_mes_ano", 
    each ([ano] - AnoMin) * 12 + [mes]
  ), 
  Tipo_Indice_Mes_Ano = Table.TransformColumnTypes(Indice_Mes_Ano, {{"indice_mes_ano", Int64.Type}}), 
  #"Linhas Classificadas" = Table.Sort(Tipo_Indice_Mes_Ano, {{"Data", Order.Descending}})
in
  #"Linhas Classificadas"

Copie este código, abra o Power Query, crie uma consulta nula e vá no editor avançado para substituir o código por este.

E se você tiver curiosidade em saber o passo a passo de como criar a tabela calendário, tem um artigo no meu blog sobre o assunto:
Tabela Calendário no Power BI – Rafa Lemos

Tabelas criadas, agora é preciso criar os relacionamento entre as datas da tabela fato e tabela dimensão calendário:

Primeiro criei o relacionamento d_calendario[Data] 1 – * f_vendas[data_fatura] que está ativo.
Na sequência criei o relacionamento d_calendario[Data] 1 – * f_vendas[data_vencimento] que está inativo.

A diferença entre relacionamento ativo e inativo é o tracejado da linha.
Quando a linha é sólida, significa que o relacionamento está ativo e o tracejado é inativo.

A criação do relacionamento inativo é essencial para utilizarmos a função USERELATIONSHIP.
É a partir deste relacionamento que informaremos ao Power BI que a medida precisa filtrar a tabela fato pela data_vencimento.

Com a criação dos relacionamentos, agora podemos pensar nas medidas.

Neste exemplo eu criei duas medidas.
A primeira com visão do valor faturado por competência e a segunda com visão de vencimento, conforme podemos ver na imagem abaixo:

E para finalizar, seguem as medidas criadas:

Total Faturado = 
SUM(f_vendas[valor])

Total Contas a Receber =
CALCULATE (
    SUM ( f_vendas[valor] ),
    USERELATIONSHIP ( d_calendario[Data], f_vendas[data_vencimento] )
)

Como você pode ver, na medida “Total Contas a Receber” utilizei a função USERELATIONSHIP e informe quais colunas que possuem relacionamento inativo que a medida precisa considerar.

Por isso que os valores do Total Contas a Receber é diferente do Total Faturado na comparação mensal.

Para a formação dos códigos utilizei dois sites:
M LANGUAGE: Power Query Formatter | Beautify your PowerQuery code
DAX: DAX Formatter by SQLBI

[Power BI] Userelationship – Como trabalhar com data de faturamento e data de vencimento na tabela FATO