A linha azul alisa flutuações aleatórias, diz uma tendência menos sobre-reativa Eu realizei recentemente que este tópico foi coberto nunca antes, em sua forma a mais direta, neste local Na verdade, era o assunto de Um post convidado pelo estimado David Churchward. E também pelo igualmente estimado Kasper de Jonge. Mas nenhum desses postos beneficiou das funções v2 disponíveis para nós hoje). Para ilustrar o que podemos fazer com fórmulas Power Pivot de última geração, vamos começar com este modelo simples: E um pivô simples: A unidade vendida é a linha vermelha irregular no gráfico no topo da mensagem e Sua fórmula é muito simples: Unidades Vendidas SUM (SalesQtySold) E nós queremos uma versão de Unidades Vendidas que é suavizada ao longo de um período de 3 meses. Moving Sum Começa com uma fórmula que é uma soma dos últimos 3 meses (incluindo o atual): 3 Month Moving Sum Unidades vendidas CALCULATE (Unidades vendidas, DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), - 3, Month) E ver o que se parece com: Mover Soma de 3 meses reflete o mês atual e os dois meses anteriores Mover média Primeira tentativa OK, mas esse número é maior do que um único mês e não corresponde à escala do nosso negócio do mundo real, por isso nós Wouldnt querem gráfico que queremos a versão média do que. É uma quantia móvel de 3 meses, de modo a obter a média, poderíamos apenas dividir por 3: 3 Mês Divisão média 3 3 meses Movendo Soma Unidades Vendidas 3 Qual parece: 3 Mês Movendo Médio Via Divide por 3 Tem um Drawback Aqueles primeiros Dois meses, uma vez que são os dois primeiros meses em nosso calendário, estão somando menos de 3 meses de vendas, mas ainda dividindo por 3. Então, injustamente impulsiona a sua média. Média Móvel Corrigida Podemos explicar isso mudando o nosso denominador para usar uma lógica semelhante ao numerador: 3 Mês Mover Média Corrigida 3 Mês Mover Soma Unidades Vendidas CALCULATE (DISTINCTCOUNT (CalendarYear Month), DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), - 3, mês)) Em inglês: tomar a medida de soma de 3 meses que já temos e dividi-la pelo número de meses distintos (únicos) que temos sobre esse mesmo período de 3 meses. Este Calc é mais justo aos meses nas variações do começo Há um número de variações nesta aproximação dailyweeklyquarterly versões, corrigindo para calendários que estendem além do intervalo das datas onde você tem vendas, adaptando o aos calendários feitos sob encomenda através da fórmula a mais grande no Mundo. Etc, mas vou esperar e ver o que as pessoas perguntam sobre os comentários antes de cavar em qualquer um desses. Funciona But8230 .. Eu estou trabalhando com dias 8211 especificamente, eu preciso obter uma média de 28 dias de rolamento de uma das minhas medidas. Isso funciona bem quando eu exibir o resultado com meu atributo de data. No entanto, a dimensão do meu calendário também tem um atributo discreto e não contínuo chamado DateMonth (que se parece com 1-Jan, 2-Jan etc.) Eu estava esperando que isso me permitiria então pegar meu atributo de ano e comparar a Média de 28 dias para O primeiro de janeiro através de vários anos. Assim você poderia ter um gráfico de linha onde o eixo é 1-Jan to 31-Dec (sem referência ao ano) ea categoria série é por ano, então uma linha para cada ano. Espero que alguém poderia ajudar A última equação fornecida 8211 Moving Average Corrigido 8211 oferece um denominador com um valor de 12 (ou seja, é dependente de quando meu Calendaryear mês começa. Então, para ter uma correta 12 meses de média móvel, os meus dados e calendário precisa Iniciar ao mesmo tempo). Todas as sugestões que eu descobri: O que eu fiz foi começar a contar meus meses a partir da primeira data de minhas vendas (ou seja, 40663 é a última data antes de eu querer começar a contar os meses em que minhas vendas foram). Espero que isso possa ajudar alguém a procurar neste post do blog William, você é uma poupança de vida. Eu estava tentando descobrir por que meu denominador mantido 3 para o mês mais antigo de dados que eu estava trabalhando. Agradeço seu exemplo e explicação que se aplicam exatamente à minha situação. John pullin diz: Eu tenho uma melhor solução para o comentário final aqui 8211 um que calcula o número de meses para dividir, se os seus dados e calendário não iniciar ao mesmo tempo. Esta foi a calc original: Mês de 3 meses Mover Mover Moeda Mover Unidades vendidas CALCULATE (DISTINCTCOUNT (CalendarYear mês), DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), - 3, mês)) E dentro do divisor que a edição é necessária 8211 para determinar corretamente onde dividir por 2 e depois 1 (para os primeiros 3 meses em seus dados). O seguinte funciona um deleite: 3 Mês Movendo Média 8211 Trabalhando 3 Mês Mover Soma CALCULAR (CALCULATE (COUNTROWS (VALORES (DimDateCalendarMonth)), DATESINPERIOD (DimDateDatekey, LASTDATE (DimDateDatekey), - 3, MÊS)) Onde FactSales é seu Tabela de fato obvs. Espero que isso ajude alguém fora 8211 como ele me levou louco Vikas Gautam diz: Eu tenho uma pergunta. E se precisarmos da média dos últimos 30 dias de negociação ou dias úteis. Naturalmente, nós teríamos uma coluna da data que representa negociar dias. E quantidade de vendas correspondente. 1 mês de média com a sua fórmula won8217t ser a mesma coisa. Como Exact 30 dias8217s média é necessária aqui. Você poderia por favor sugerir uma fórmula. O difícil é que o cálculo se baseie na data financeira diferente da data do calendário, uma vez que quase todas as funções existentes de data inteligente não podem ser utilizadas. Qualquer fórmula de cálculo de amostra com base na data financeira eu tenho uma abundância de informações sobre os cálculos com base nas datas do cliente a partir do link acima 8220Greatest Fórmula no mundo8221. It8217s muito helpful8230 .. Esta é uma grande postagem por todos. Eu quero saber o que se as vendas em um dos últimos três meses é zero ou nenhuma venda em tudo, então como as fórmulas sugeridas trabalho. Eu quero descobrir o número de meses a partir do mês atual para últimos três meses com vendas. Digamos que estou em maio de 2017 nos últimos três meses estará incluindo maio de 2017. março de 2017, abril de 2017 e maio de 2017. Mas abril de 2017 não tem vendas. Portanto, deve ser a soma de março e maio de 2017 vendas dividido por 2 e não 3. Suponha que temos vendas para Jun 2017 e Jul 2017. Para junho de 2017 a média dos últimos 3 meses deve ser (My 2017 Jun 2017) 2. Para Jul 2017 A média dos últimos 3 meses deve ser (May 2017 Jun 2017 Jul 2017) 3. Qualquer ajuda para alcançar este objectivo será muito apreciada. Ei, solução brilhante. Mas agora estou procurando a mesma adição que CheenuSing mencionou. Algumas séries em meus dados don8217t têm vendas no último período, mas outros fazem. CheenuSing, você encontrou a solução para que I8217d ser mais grato, obrigado. Im novo com todas as funções DAX e bastante lutando com o meu cálculo de média móvel. Eu só preciso da média móvel de 3 meses de volta não incluindo o mês atual. Eu tentei usar 8220IF8221 mas nunca funcionou. Alguém pode me ajudar com ele? Servidor SQL Denali PowerPivot Alberto Ferrari já escreveu sobre o cálculo de médias móveis em DAX usando uma coluna calculada. I8217d gostaria de apresentar uma abordagem diferente aqui usando uma medida calculada. Para a média móvel I8217m calculando uma média móvel diária (nos últimos 30 dias) aqui. Para o meu exemplo, I8217m usando o PowerPivot pasta de trabalho que pode ser baixado como parte do SSAS Tabular Modelos de Projetos Denali CTP 3 amostras. Neste post, I8217m desenvolvendo a fórmula passo a passo. No entanto, se você estiver com pressa, você pode querer diretamente para saltar para os resultados finais abaixo. Com o ano de calendário de 2003 no filtro, a data nas colunas eo montante das vendas (da tabela Internet Sales) nos detalhes, os dados de exemplo se parecem com isto: Em cada contexto row8217s, a expressão DateDate dá o contexto atual, ou seja, a data para essa linha . Mas a partir de uma medida calculada não podemos se referir a essa expressão (como não há nenhuma linha atual para a tabela Date), em vez disso, temos que usar uma expressão como LastDate (DateDate). Assim, a fim de obter os últimos trinta dias, podemos usar esta expressão Podemos agora resumir as nossas vendas pela Internet para cada um desses dias, usando a função de resumo: Resumir (160 DatasInPeriod (DateDate, LastDate (DateDate), - 30, DAY) 160, DateDate 160. quotSalesAmountSumquot 160. Sum (Internet SalesSales Amount)) E, finalmente, we8217re usando a função DAX AverageX para calcular a média desses 30 valores: Vendas Montante (30d média): AverageX (160 Summarize (160160160 DatesInPeriod (DateDate, LastDate (DataData), - 30, DAY) 160160160, DateDate 160160160. quotSalesAmountSumquot 160160160. Soma (Internet SalesSales Amount) 160) 160, SalesAmountSum) Este é o cálculo que estamos usando na nossa tabela de vendas pela Internet, como mostrado na imagem abaixo: Ao adicionar este cálculo à tabela dinâmica a partir de cima, o resultado é o seguinte: Observando o resultado, parece que não temos dados antes de 1 de Janeiro de 2003: O primeiro valor para a média móvel é idêntico ao valor do dia Há ar E nenhuma linha antes dessa data). O segundo valor para a média móvel é realmente a média dos dois primeiros dias e assim por diante. Isso não é bem correto, mas volto a esse problema em um segundo. A captura de tela mostra o cálculo da média móvel de 31 de janeiro como a média dos valores diários de 2 a 31 de janeiro. Nossa medida calculada também funciona bem quando os filtros são aplicados. Na seguinte captura de tela usei duas categorias de produtos para a série de dados: Como nossa medida calculada funciona em níveis de agregação mais altos Para descobrir, I8217m usando a hierarquia de Calendário nas linhas (em vez da data). Para simplificar, removi os níveis de semestre e trimestre usando as opções da tabela dinâmica do Excel8217s (opção Mostrar campos ocultos). Como você pode ver, o cálculo ainda funciona bem. Aqui, o agregado mensal é a média móvel para o último dia do mês específico. Você pode ver isso claramente para janeiro (valor de 14,215.01 também aparece na imagem acima como o valor para 31 de janeiro). Se este foi o requisito de negócio (o que parece razoável para uma média diária), então a agregação funciona bem em um nível mensal (caso contrário, teremos de ajustar o nosso cálculo e este será um tópico da próxima postagem). Mas, embora a agregação faça sentido em um nível mensal, se expandirmos essa visão para o nível do dia, veremos que nossa medida calculada simplesmente retorna o valor das vendas para esse dia, e não a média dos últimos 30 dias: Como isso pode ser. O problema resulta do contexto em que calculamos nossa soma, conforme destacado no seguinte código: Valor de Vendas (30d média): MédiaX (160 Resumir (160160160 datasinperiod (DateDate, LastDate (DateDate), - 30, DAY) 160160160, DateDate 160160160. quotSalesAmountSumquot 160160160. Soma (Internet SalesSales Quantidade) 160) 160, SalesAmountSum) Uma vez que avaliamos esta expressão durante o período datas dado, o único contexto que é sobrescrito aqui é DateDate. Em nossa hierarquia, usamos diferentes atributos de nossa dimensão (ano civil, mês e dia do mês). Como esse contexto ainda está presente, o cálculo também é filtrado por esses atributos. E isso explica por que o contexto do dia atual ainda está presente para cada linha. Para deixar as coisas claras, desde que avaliamos esta expressão fora de um contexto de data, tudo está bem conforme a seguinte consulta DAX é exibida ao ser executada pelo Management Studio na perspectiva Internet Sales de nosso modelo (usando o banco de dados tabular com os mesmos dados ) (160160160160160160 datasinperiod (DateDate, data (2003,1,1), - 5, DAY) 160160160160160160160, DateDate 160160160160160160160. quotSalesAmountSumquot 160160160160160160160. Soma (Internet SalesSales Quantidade) 160160160)) Aqui, eu reduzi o período de tempo Para 5 dias e também definir uma data fixa como LastDate (8230) resultaria na última data da minha tabela de dimensão de data para a qual não há dados estão presentes nos dados de exemplo. Aqui está o resultado da consulta: No entanto, após definir um filtro para 2003, nenhuma linha de dados fora de 2003 será incluída na soma. Isto explica a observação acima: Parecia que só temos dados a partir de 1 de Janeiro de 2003. E agora, sabemos porquê: O ano de 2003 estava no filtro (como você pode ver na primeira tela deste post) e Portanto, estava presente no cálculo da soma. Agora, tudo o que temos a fazer é se livrar desses filtros adicionais porque we8217re já filtrar os nossos resultados por Data. A maneira mais fácil de fazer isso é usar a função Calcular e aplicar ALL (8230) para todos os atributos para os quais queremos remover o filtro. Como temos alguns desses atributos (ano, mês, dia, semana, 8230) e queremos remover o filtro de todos eles, mas o atributo de data, a função de atalho ALLEXCEPT é muito útil aqui. Se você tem um fundo MDX você vai se perguntar por que don8217t obter um problema semelhante ao usar SSAS no modo OLAP (BISM Multidimensional). A razão é que o nosso banco de dados OLAP tem relacionamentos de atributo, portanto, depois de definir o atributo de data (chave), os outros atributos também são alterados automaticamente e não precisamos nos preocupar com isso (veja meu post aqui). Mas no modelo tabular nós não temos relacionamentos de atributo (nem mesmo um verdadeiro atributo de chave) e, portanto, precisamos eliminar filtros indesejados de nossos cálculos. Então aqui estamos com o valor de vendas 8230 (30d média): AverageX (160 Summarize (160160160 datasinperiod (DateDate, LastDate (DateDate), - 30, DAY) 160160160, DateDate 160160160. quotSalesAmountSumquot 160160160. calcular (Soma (Internet SalesSales Amount) , ALLEXCEPT (Date, DateDate)) 160), SalesAmountSum) E esta é a nossa última tabela dinâmica no Excel: Para ilustrar a média móvel, aqui está o mesmo extrato de dados em uma vista de gráfico (Excel): Embora filtrado nossos dados em 2003, a média móvel dos primeiros 29 dias de 2003 toma devidamente em conta os dias correspondentes de 2002. Você reconhecerá os valores para 30 e 31 de janeiro de nossa primeira aproximação porque estes eram os primeiros dias para que nosso primeiro cálculo teve uma quantidade suficiente de dados (cheio 30 dias). Eu estou tentando criar uma média movente em meu modelo. Procurando alguma ajuda. Eu tentei seguir os detalhes em Alberto Ferraris blog aqui. Mas eu não poderia obter a medida DayNumber trabalhando, a sintaxe não parecia correta e eu não poderia corrigi-lo. Meu modelo tem uma tabela de fatos contendo uma lista de casos, juntando-se a uma tabela de data através de data criada. Eu tenho um segundo relacionamento (Inativo) para a tabela Data na coluna ClosedDate. Eu tenho uma medida: Caso Contagem Fechada: CALCULAR (COUNTROWS (Caso), USERELATIONSHIP (CaseClosedDateKey, DateDateKey)). Eu gostaria de uma medida obtendo a soma de Case Closed Count para os últimos três dias do contexto atual. Eu planejo dividir esse número por 3 para obter a média móvel de 3 dias. Uma outra parte da lógica que eu gostaria de considerar - se o último dia é HOJE, então os 3 dias anteriores são usados - os dados são atualizados a cada 15 minutos, então fazendo isso às 09:00 da manhã seria distorcer a média, como Não é um dia totalmente concluído. Qualquer ajuda é apreciada. Domingo, 17 de fevereiro de 2017 17:25 Heres um link para uma abordagem usando apenas uma medida calculada que Javier Guillen escreveu um tempo atrás. Espero que ajude. Brent Greenwood, MS, MCITP, CBIP Por favor, marque as respostas corretas e mensagens úteis brentgreenwood. blogspot Editado por Brent Greenwood Editor segunda-feira, 18 de fevereiro de 2017 16:08 Proposta como resposta por Ed Price - MSFT Microsoft empregado, 2017 19:39 Marcado como resposta por Ed Price - MSFT Funcionário da Microsoft, Proprietário Tuesday, September 17, 2017 6:39 PM Monday, February 18, 2017 4:08 PM Em seu post, Alberto faz uso da função EARLIER que retorna Um valor de um contexto de linha anterior. Isso funciona somente em uma expressão de iteração, quando essa expressão é avaliada em um contexto de linha existente (uma outra expressão de iteração ou uma coluna calculada.) O seguinte atende ao requisito (não testado) Caso Contagem Fechada - Últimos 3 dias: CALCULAR ( (DataDateKey, -3, Day). DateDateKey)) O último pode ser feito com uma expressão IF usando a função TODAY () e adaptando o padrão acima. Proposta como resposta por Ed Price - MSFT Microsoft empregado, Proprietário quinta-feira, 22 de agosto de 2017 12:40 PM Heres um link para uma abordagem usando apenas uma medida calculada que Javier Guillen escreveu um tempo atrás . Espero que ajude. Brent Greenwood, MS, MCITP, CBIP Por favor, marque as respostas corretas e mensagens úteis brentgreenwood. blogspot Editado por Brent Greenwood Editor segunda-feira, 18 de fevereiro de 2017 16:08 Proposta como resposta por Ed Price - MSFT Microsoft empregado, Feira, 17 de setembro de 2017 PMSQL Servidor Denali PowerPivot Alberto Ferrari já escreveu sobre o cálculo de médias móveis em DAX Usando uma coluna calculada. Gostaria de apresentar uma abordagem diferente aqui usando uma medida calculada. Para a média móvel estou calculando uma média móvel diária (nos últimos 30 dias) aqui. Para meu exemplo, estou usando a pasta de trabalho do PowerPivot que pode ser baixada como parte dos Projetos de Modelo Tabular SSAS das amostras de Denali CTP 3. Neste post, estou desenvolvendo a fórmula passo a passo. No entanto, se você estiver com pressa, você pode querer diretamente para saltar para os resultados finais abaixo. Com o ano de calendário de 2003 no filtro, a data nas colunas e o montante das vendas (da tabela Internet Sales) nos detalhes, os dados de exemplo têm esta aparência: Em cada contexto de linhas, a expressão 8216DateDate fornece o contexto atual, ou seja, a data dessa linha . Mas a partir de uma medida calculada não podemos referir-se a esta expressão (como não existe nenhuma linha actual para a tabela de data), em vez disso, temos de utilizar uma expressão como LastDate (8216DateDate). Assim, para obter os últimos trinta dias, podemos usar essa expressão. Agora podemos resumir nossas vendas pela Internet para cada um desses dias, usando a função resumir: Summarize (DatesInPeriod (8216DateDate, LastDate (8216DateDate), - 30, DAY) 8217DateDate 8220SalesAmountSum8221 Sum (8216Internet SalesSales Amount)) E finalmente, estavam usando a função DAX AverageX para calcular a média desses 30 valores: Vendas Montante (30d média): AverageX (Sumário (DatasInPeriod (8216DateDate, LastDate (8216DateDate) Soma (8216Internet SalesSales Amount)), SalesAmountSum) Este é o cálculo que estamos usando na nossa tabela de vendas pela Internet, como mostrado na imagem abaixo: Ao adicionar esse cálculo à tabela dinâmica de cima, O resultado é o seguinte: Observando o resultado, parece que não temos dados antes de 1º de janeiro de 2003: O primeiro valor da média móvel é idêntico ao valor do dia (não há linhas antes dessa data). O segundo valor para a média móvel é realmente a média dos dois primeiros dias e assim por diante. Isso não é muito correto, mas estou voltando a este problema em um segundo. A captura de tela mostra o cálculo da média móvel de 31 de janeiro como a média dos valores diários de 2 a 31 de janeiro. Nossa medida calculada também funciona bem quando os filtros são aplicados. Na seguinte captura de tela usei duas categorias de produtos para a série de dados: Como nossa medida calculada funciona em níveis de agregação mais altos Para descobrir, estou usando a hierarquia do Calendário nas linhas (em vez da data). Para simplificar, removi os níveis de semestre e trimestre usando as opções da tabela dinâmica Excels (opção Mostrar campos ocultos). Como você pode ver, o cálculo ainda funciona bem. Aqui, o agregado mensal é a média móvel para o último dia do mês específico. Você pode ver isso claramente para janeiro (valor de 14,215.01 também aparece na imagem acima como o valor para 31 de janeiro). Se este foi o requisito de negócio (o que parece razoável para uma média diária), então a agregação funciona bem em um nível mensal (caso contrário, teremos de ajustar o nosso cálculo e este será um tópico da próxima postagem). Mas embora a agregação faça sentido em um nível mensal, se expandirmos essa visão para o nível do dia você verá que nossa medida calculada simplesmente retorna o valor das vendas para esse dia, não a média dos últimos 30 dias mais: Como isso pode ser. O problema resulta do contexto em que calculamos nossa soma, conforme destacado no código a seguir: Valor de Vendas (30d média): AverageX (Resumir (datasinperiod (8216DateDate, LastDate (8216DateDate), - 30, DAY), 8217DateDate. 8220SalesAmountSum8221. Sum (8216Internet SalesSales Amount)), SalesAmountSum) Uma vez que avaliamos esta expressão durante o período datas dado, o único contexto que é sobrescrito aqui, é 8216DateDate. Em nossa hierarquia estavam usando atributos diferentes de nossa dimensão (ano civil, mês e dia do mês). Como esse contexto ainda está presente, o cálculo também é filtrado por esses atributos. E isso explica por que o contexto dos dias atuais ainda está presente para cada linha. Para deixar as coisas claras, desde que avaliamos esta expressão fora de um contexto de data, tudo está bem conforme a seguinte consulta DAX é exibida ao ser executada pelo Management Studio na perspectiva Internet Sales de nosso modelo (usando o banco de dados tabular com os mesmos dados ) (8216DateDate, date (2003,1,1), - 5, DAY), 8217DateDate. 8220SalesAmountSum8221.Som (8216Internet SalesSales Amount))) Aqui, eu reduzi o período de tempo para 5 dias e também definir Uma data fixa como LastDate () resultaria na última data da minha tabela de dimensão de data para a qual não existem dados nos dados de exemplo. Aqui está o resultado da consulta: No entanto, após definir um filtro para 2003, nenhuma linha de dados fora de 2003 será incluída na soma. Isto explica a observação acima: Parecia que só temos dados a partir de 1 de Janeiro de 2003. E agora, sabemos porquê: O ano de 2003 estava no filtro (como você pode ver na primeira tela deste post) e Portanto, estava presente no cálculo da soma. Agora, tudo o que temos a fazer é se livrar desses filtros adicionais porque já estavam filtrando os nossos resultados por Data. A maneira mais fácil de fazer isso é usar a função Calculate e aplicar ALL () para todos os atributos para os quais queremos remover o filtro. Como temos alguns desses atributos (Ano, Mês, Dia, Dia da Semana,) e queremos remover o filtro de todos eles, mas o atributo de data, a função de atalho ALLEXCEPT é muito útil aqui. Se você tem um fundo MDX você vai saber por que não temos um problema semelhante ao usar SSAS no modo OLAP (BISM Multidimensional). A razão é que nosso banco de dados OLAP tem relacionamentos de atributo, portanto, após a definição do atributo de data (chave), os outros atributos também são alterados automaticamente e não precisamos nos preocupar com isso (veja minha postagem aqui). Mas no modelo tabular não temos relações de atributo (nem mesmo um verdadeiro atributo de chave) e, portanto, precisamos eliminar filtros indesejados de nossos cálculos. Então aqui estamos com o valor de vendas (média de 30d): AverageX (sumário (datasinperiod (8216DateDate, LastDate (8216DateDate), - 30, DAY), 8217DateDate. 8220SalesAmountSum8221. Calcular (Soma (8216Internet SalesSales Amount), ALLEXCEPT (8216Date8217,8217DateDate ) Esta é a nossa tabela dinâmica final no Excel: Para ilustrar a média móvel, aqui está o mesmo extrato de dados em uma exibição de gráfico (Excel): Embora filtrado nossos dados em 2003 a média móvel para o primeiro 29 dias de 2003 corretamente leva em conta os dias correspondentes de 2002. Você reconhecerá os valores para 30 e 31 de janeiro de nossa primeira abordagem, pois foram os primeiros dias para os quais nosso primeiro cálculo teve uma quantidade suficiente de dados (30 dias completos).
No comments:
Post a Comment