Criando uma tabela virtual para segmentar a aplicação de um filtro

[Bruno Crabi Figueiredo Crabi .]

Fala Pessoal, boa noite!

Já tem um bom tempo que estou tentando fazer esta segmentação porém ainda sem sucesso. Vejam se conseguem me ajudar no seguinte cenário:

O projeto faz a comparação de faturamento entre várias Empresa por Mês Base. Estas empresas possuem várias Lojas. Desta forma, a análise de faturamento médio por empresa ( TOTAL FATURADO ÷ TOTAL DE LOJAS ) é realizada de 3 formas/períodos distintos: 

A) Mês Base X Mesmo período Ano Anterior ( EX.: set/23 X set/22 ) 
B) Acumulado do ano até o Mês-Base X Mesmo período Ano Anterior  ( EX.: Jan/23 à set/23 X Jan/22 à set/22 - Quando seleciona o mês base de setembro/23 )
C) Acumulado 12 últimos meses X Mesmo período Ano Anterior ( EX.: Out/22 à set/23 X out/21 à set/22 - Quando seleciona o mês base de setembro/23 )

Até ai foi bem tranquilo e consegui fazer sem grandes complicações. 

No entanto, foi pedido para adicionar um "botão" ( estou usando o toggle ) para filtrar as "Mesmas Lojas" dentro daqueles 3 grupos de comparação de períodos ( A, B e C ), ou seja, quando este filtro for ativado, ele deve filtrar apenas as Lojas, de cada empresa, que tiveram faturamento em todos os períodos.

Veja como é o critério deste filtro usando os períodos citados acima como exemplo:
A) Mês Base X Mesmo período Ano Anterior ( EX.: set/23 X set/22 )  -> Todas as lojas que tiveram faturamento em set/23 E em set/22. Veja que se em set/22 uma loja teve faturamento e em set/23 não tiver, ela não deve ser considerada. Também não pode o inverso, ou seja, se uma loja tiver faturamento em set/23 e não tiver em set/22.
---- OS DOIS PRÓXIMOS SAO OS MAIS DIFICEIS ----
B) Acumulado do ano até o Mês-Base X Mesmo período Ano Anterior  ( EX.: Jan/23 à set/23 X Jan/22 à set/22 - Quando seleciona o mês base de setembro/23 ). Aqui deve-se considerar apenas as lojas que tiveram faturamento em todos os períodos, ou seja, de  Jan/22 à set/22 e de Jan/23 à set/23. Se em algum mês parou de ter faturamento não pode-se considerar nada de faturamento para esta loja. 
C) Acumulado 12 últimos meses X Mesmo período Ano Anterior ( EX.: Out/22 à set/23 X out/21 à set/22 - Quando seleciona o mês base de setembro/23 ) Este é basicamente igual ao anterior, ou seja, tem de considerar as lojas que tiveram faturamento em todos os últimos 24 meses de acordo com o mês base selecionado ( de out/21 a set/23 )

Vejam na imagem os meses que as Lojas da Empresa A possui faturamento. A loja 29, por exemplo, tem faturamento apenas por um período ( jul/22 a set/23 ). Já a loja 1 possui faturamento em todos os períodos.

Ficaria muito grato se pudessem me ajudar pois já tentei inúmeras maneiras, mas nenhuma que foi de fato uma solução concreta. 

Caso não tenham entendido, fiquem a vontade para questionar. 

Muito obrigado. dados.xlsx

[Erick Oliveira]

Boa tarde, @Bruno Crabi Figueiredo Crabi .;

Analisando sua solicitação percebi o seguinte: selecionando na segmentação o mês de dez/23, teríamos os seguintes períodos para suas condições:

a) (Dez/23 x Dez/22)

b) (Jan/23 à Dez/23 x Jan/22 à Dez/22)

c) (Jan/23 à Dez/23 x Jan/22 à Dez/22)

Então a medida basicamente analisa todos esses 24 meses (entre Jan/22 até Dez/32) e verifica se em cada um desses a loja possui faturamento, através da função CONTAIS que verifica se a tabela virtual que criei possui valores em branco.

O exemplo que comentou da EMPRESA 2, ele não passa nessas condições se selecionar dez/23, por que todas as lojas possuem valores em branco em meses no intervalo citado acima:

No arquivo em anexo adicionei a condição do botão e o filtro no visual.

 

Mesmas Lojas.pbix

[Bruno Crabi Figueiredo Crabi .]

@Erick Oliveira, 

Perfeita sua análise. Realmente estava com este período de dados faltando.

No entanto, para os 3 cenários, tive que adaptar para considerar um total de meses diferente para cada cenário ( 13 para o A, 25 para o B e 24 para o C). Mas foi algo q consegui fazer aqui no código. 

Te agradeço muito por conseguir me auxiliar, principalmente pela sua agilidade em responder.

Realmente fazia muito tempo q estava buscando e tentando solucionar. Este seu código me abriu a mente e consegui vislumbrar maneiras diferentes de pensar as soluções.

Grande abraço! 

[Erick Oliveira]

Boa noite @Bruno Crabi Figueiredo Crabi .;

Use a medida abaixo que retorna 1 para os itens que passam na condição solicitada:

Filtro = 
VAR _DataAtual =
    MAX ( dCalendario[Data] )
VAR _AnoAnterior =
    EOMONTH ( _DataAtual, -24 ) + 1
VAR _TabelaDatas =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( dCalendario ),
                AND ( dCalendario[Data] <= _DataAtual, dCalendario[Data] >= _AnoAnterior )
            ),
            "AnoMes", [AnoMesINT]
        )
    )
RETURN
    INT (
        NOT CONTAINS (
            ADDCOLUMNS (
                _TabelaDatas,
                "@Faturamento",
                    VAR _AnoMes = [AnoMes]
                    RETURN
                        CALCULATE (
                            [Faturamento],
                            // Medida com valor faturado
                            ALL ( dCalendario ),
                            KEEPFILTERS ( dCalendario[AnoMesINT] = _AnoMes )
                        )
            ),
            [@Faturamento], BLANK ()
        )
    )

Depois aplique um filtro no visual:

 

Segue um arquivo de exemplo em anexo.

Criando uma tabela virtual para segmentar a aplicação de um filtro.pbix

[Bruno Crabi Figueiredo Crabi .]

@Erick Oliveira, boa tarde! Tudo bem?

Obrigado por tentar me ajudar. No entanto, confesso que não entendi por completo a parte após o "RETURN".
Desta forma, tomei a liberdade de trazer o arquivo q estou utilizando para que se possível possa me ajudar. 
Veja que ao adicionar a medida "FILTRO", para a Empresa 2, mesmo possuindo faturamento no período selecionado ele não retorna "1". 

Além disso, preciso que ao clicar no toggle ( como pode ver no arquivo ) seja possível adicionar o valor "1" da medida FILTRO dentro das medidas de faturamento, sem ir no visual de FILTROS conforme vc sugeriu. Imaginei  repetir o código da medida FILTRO dentro de cada medida de faturamento, inserindo como uma variável, e criar uma condição para que quando ativado o toggle aplique esta condição.
 

Mesmas Lojas.pbix

[Bruno Crabi Figueiredo Crabi .]

@Erick Oliveira, boa tarde!

Desculpe abusar da sua gentileza, mas percebi que ao retirar o campo fVendas[codLoja] da matriz, o filtro "Mesmas Lojas" não funciona.
Como poderia aplicar esta granularidade mesmo quando retirando este campo ? Um outro local também que não funciona seria na utilização de gráficos onde aparece apenas o nome das Empresas e não as lojas.



Veja que aplicando o mesmo filtro, mas retirando o campo fVendas[codLoja] ele não considera mais o filtro. 

Mesmas Lojas.pbix

[Erick Oliveira]

Boa tarde @Bruno Crabi Figueiredo Crabi .;

 

Nesse caso o filtro não poderia ser aplicado no visual, e sim na tabela virtual na medida. Seria assim:

 

Faturamento com Filtro = 
VAR _DataAtual =
    MAX ( dCalendario[Data] )
VAR _AnoAnterior =
    EOMONTH ( _DataAtual, -24 ) + 1
VAR _TabelaMeses =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( dCalendario ),
                AND ( dCalendario[Data] <= _DataAtual, dCalendario[Data] >= _AnoAnterior )
            ),
            "AnoMes", [AnoMes]
        )
    )
RETURN
    CALCULATE (
        [Faturamento], // Medida
        FILTER (
            ADDCOLUMNS (
                CROSSJOIN ( VALUES ( fVendas[Empresa] ), VALUES ( fVendas[CodLoja] ) ),
                "@MesesCompletos",
                    NOT CONTAINS (
                        ADDCOLUMNS (
                            _TabelaMeses,
                            "@Faturamento",
                                VAR _AnoMes = [AnoMes]
                                RETURN
                                    CALCULATE (
                                        [Faturamento], // Medida de faturamento
                                        ALL ( dCalendario ),
                                        KEEPFILTERS ( dCalendario[AnoMes] = _AnoMes )
                                    )
                        ),
                        [@Faturamento], BLANK ()
                    )
            ),
            [@MesesCompletos]
        )
    )

Recomendo criar uma tabela fixa para cada etapa do código para melhorar o entendimento do que foi feito.Mesmas Lojas (1).pbix

[Erick Oliveira]

Boa tarde @Bruno Crabi Figueiredo Crabi .;

Dei uma simplificada na medida com o mesmo resultado, segue:

Faturamento Revisao = 
CALCULATE (
    [Faturamento],
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( fVendas[Empresa] ), VALUES ( fVendas[CodLoja] ) ),
            "@MesesConsecutivos",
                COUNTROWS (
                    FILTER (
                        WINDOW (
                            -23, REL,
                            0, REL,
                            ALL ( dCalendario[Mês/Ano], dCalendario[AnoMes] ),
                            ORDERBY ( dCalendario[AnoMes], ASC )
                        ),
                        [Faturamento]
                    )
                )
        ),
        [@MesesConsecutivos] = 24
    )
)