Tabela de DATAS

[Gerson Faria]

Bom dia!

 

Eu tenho dois arquivos PBIX. Nos dois arquivos eu tenho uma tabela dimensão de datas. Porém, num arquivo ela foi criada a partir de uma planilha do Excel e no outro foi criado através da linguagem M.

Nos dois arquivos eu identifiquei o que é dia útil. Foi criada uma coluna [ÚTIL] onde 0 (zero) significa dia não útil e 1 (um) significa dia útil. Até aí, os dois arquivos funcionaram igualmente bem.

Eu preciso apurar nos dois arquivos o faturamento por dia útil. Só que, eventualmente, existem faturamentos em dias não úteis. O conceito da empresa para estes casos é que o faturamento do dia não útil deve ser somado ao faturamento do primeiro dia útil imediatamente anterior a esse dia não útil, desde que esse dia útil esteja no mesmo mês. Se não houver dia útil anterior no mesmo mês, deve ser somado ao primeiro dia útil posterior.

Eu criei uma coluna que identifica em qual dia útil um eventual faturamento em dia não útil deve ser atribuído, usando DAX, como segue:

DIA FATURAMENTO =

var vOntem = PREVIOUSDAY(DATAS[DATA])

var vAmanha = NEXTDAY(DATAS[DATA])

var vDiaUtilAnterior =

CALCULATE(MAX(DATAS[DATA]),

DATAS[ÚTIL] <> 0,

DATAS[DATA] <= vOntem

)

var vDiaUtilPosterior =

CALCULATE(min(DATAS[DATA]),

DATAS[ÚTIL] <> 0,

DATAS[DATA] >= vAmanha

)

RETURN

IF(

DATAS[ÚTIL] <> 0,

DATAS[DATA],

IF(

DATAS[Mês] = month(vDiaUtilAnterior),

vDiaUtilAnterior,

vDiaUtilPosterior

)

)

 

O meu problema é o seguinte, no arquivo em que a tabela de datas tem origem numa planilha de Excel funcionou perfeitamente. No arquivo em que a tabela de datas foi criada com a linguagem M, não funciona. Ele informa que foi criada uma referência circular. Não consegui identificar por quê. Alguém saberia me dizer o que está errado para este segundo arquivo?

[Gerson Faria]

Segue o código da dCalendario.

fCTRC é a minha fato.

let
    DataMin = List.Min(fCTRC[Data de Emissao]),
    AnoMin = Date.Year(DataMin),
    DataMax = List.Max(fCTRC[Data de Emissao]),
    AnoMax = Date.Year(DataMax),
    DataInicial = #date(AnoMin, 01,01),
    DataFinal = #date(AnoMax, 12, 31),
    Duracao = Duration.Days(DataFinal - DataInicial) + 1,
    Fonte = List.Dates(DataInicial, Duracao, #duration(1, 0, 0, 0)),
    #"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Convertido para Tabela",{{"Column1", type date}}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Tipo Alterado",{{"Column1", "Data"}}),
    #"Ano Inserido" = Table.AddColumn(#"Colunas Renomeadas", "Ano", each Date.Year([Data]), Int64.Type),
    #"Mês Inserido" = Table.AddColumn(#"Ano Inserido", "Mês", each Date.Month([Data]), Int64.Type),
    #"Nome do Mês Inserido" = Table.AddColumn(#"Mês Inserido", "Nome do Mês", each Date.MonthName([Data]), type text),
    #"Dia Inserido" = Table.AddColumn(#"Nome do Mês Inserido", "Dia", each Date.Day([Data]), Int64.Type),
    #"Nome do Dia Inserido" = Table.AddColumn(#"Dia Inserido", "Nome do Dia", each Date.DayOfWeekName([Data]), type text),
    #"Dia da Semana Inserido" = Table.AddColumn(#"Nome do Dia Inserido", "Dia da Semana", each Date.DayOfWeek([Data]), Int64.Type),
    #"Semana do Mês Inserida" = Table.AddColumn(#"Dia da Semana Inserido", "Semana do Mês", each Date.WeekOfMonth([Data]), Int64.Type),
    #"Consultas Mescladas" = Table.NestedJoin(#"Semana do Mês Inserida", {"Data"}, dFeriados, {"DATA"}, "dFeriados", JoinKind.LeftOuter),
    #"dFeriados Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas", "dFeriados", {"ÚTIL"}, {"Auxiliar - Feriados"}),
    #"Valor Substituído" = Table.ReplaceValue(#"dFeriados Expandido",null,1,Replacer.ReplaceValue,{"Auxiliar - Feriados"}),
    #"Coluna Condicional Adicionada" = Table.AddColumn(#"Valor Substituído", "Dia Útil", each if [Dia da Semana] = 0 then 0 else if [Dia da Semana] = 6 then 0 else if [#"Auxiliar - Feriados"] = 0 then 0 else [#"Auxiliar - Feriados"]),
    #"Colunas Removidas" = Table.RemoveColumns(#"Coluna Condicional Adicionada",{"Auxiliar - Feriados"}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Colunas Removidas",{{"Dia Útil", type number}})
in
    #"Tipo Alterado1"

[leoslemos]

@Gerson Faria bom dia!

Fiz o processo que foi descrito, e consegui ter êxito ao criar dCalendario com a linguagem M, dá uma olhada no PBIX anexo.

Apenas a medida estruturei de uma outra forma.

 

Solucao745.pbix

[Gerson Faria]

Bom dia, @leoslemos!

Transferi a tua solução para o meu arquivo. Só alterei os nomes de colunas da minha base de dados mas continuou dando a mensagem de referência circular.

Mas deixa eu entender o que você fez. Você criou umas variáveis a mais em relação à minha solução, mas a lógica é a mesma, certo?

Você criou a variável vDiaNaoUtil para tirar um IF do resultado e transformar o IF do resultado em SWITCH, certo?

E as outras duas variáveis - vDiaUtil e vDataAtual - tem uma função que eu não usei - SELECTEDVALUE. Seria esse o diferencial que fez a tua solução funcionar?

[leoslemos]

Referente as variáveis, sim criei por opção, prefiro criá-las, para mim fica mais fácil de ler a medida.

A sua medida também funcionou para mim, só precisei colocar SELECTEDVALUE no RETURN, porque o DAX estava "Reclamando"

Quanto a vDiaNaoUtil, sim criei para usar o SWTICH, também por opção.

*Medida Original


*Medida Original Alterada.

_ORIGINAL_DIA FATURAMENTO = 
VAR vOntem =
    PREVIOUSDAY ( DATAS[DATA] )
VAR vAmanha =
    NEXTDAY ( DATAS[DATA] )
VAR vDiaUtilAnterior =
    CALCULATE ( MAX ( DATAS[DATA] ), DATAS[ÚTIL] <> 0, DATAS[DATA] <= vOntem )
VAR vDiaUtilPosterior =
    CALCULATE ( MIN ( DATAS[DATA] ), DATAS[ÚTIL] <> 0, DATAS[DATA] >= vAmanha )
RETURN
    //Precisei colocar o SELECTEDVALUE porque o DAX estava RECLAMANDO.
    IF (
        SELECTEDVALUE( DATAS[ÚTIL] ) <> 0,
        SELECTEDVALUE( DATAS[DATA] ),
        IF (
            SELECTEDVALUE( DATAS[Mês] ) = MONTH ( vDiaUtilAnterior ),
            vDiaUtilAnterior,
            vDiaUtilPosterior
        )
    )

Você consegue disponibilizar o seu PBIX aqui, onde criou a dCalendario? 

[Gerson Faria]

O pessoal da TI não autorizou mandar o PBIX. Mas eu usei o código do curso onde a tabela de datas usa as datas da tabela fato para identificar a data inicial e final.

De qualquer forma, como a tua sugestão também não funcionou no meu arquivo, fiz algumas tentativas que acreditava que funcionaria, mas não funcionaram e outras que eu não tinha certeza que funcionariam e uma delas funcionou, mas não entendo bem por que. Abaixo tem a alteração que eu fiz na variável vDiaUtilAnterior.

ORIGINAL

var vDiaUtilAnterior = CALCULATE( MAX( DATAS[DATA] ), DATAS[ÚTIL] <> 0, DATAS[DATA] <= vOntem )

COM A FUNÇÃO FILTER

var vDiaUtilAnterior = CALCULATE( MAX( DATAS[Data] ), FILTER( DATAS, DATAS[Dia Útil] <> 0 ), FILTER( DATAS, DATAS[Data] <= vOntem ) )

Essa mesma alteração eu fiz na variável vDiaUtilPosterior e funcionou. Não compreendi. Se você puder me ajudar a entender porque funcionou eu agradeço.

 

[leoslemos]

Sem problemas, mas acredito que tenha algum problema na forma que foi criada a tabela de calendário, pois é possível que esteja com datas repetidas.

Pesquisei no fórum e encontrei um problema bem semelhante ao seu, abaixo segue o link, no arquivo que mandei eu criei uma tabela simples de calendário pelo Power Query.

Uma dependência circular foi detectada - Modelagem e relacionamentos - Comunidade Power BI Experience