Medida para calcular média de volume comprado 6 meses antes, 1 ano depois e 2 anos depois do inicio de um contrato

[Lucas Denker]

Boa tarde a todos! Trago um problema do qual não consigo resolver de maneira inteligente...
Primeiramente darei o contexto de minhas tabelas, meu problema e como havia pensado em resolver. Assim como uma imagem ilustrando o que necessito.
Em anexo vocês irão encontrar um arquivo xlsx que contém 3 bases: dContratos, dCliente e fVolume.
Contexto.
dContratos - Base com informações dos contratos realizado por cada cliente dentro da empresa, com valores de data de inicio de data de fim de contrato.
dCliente - Base com informações dos clientes da empresa dos quais podem ter contratos ou não, aqui é uma base de clientes geral da empresa.
fVolume - Base com informações de compra em volume de cada cliente e a data em que foi realizada a compra.

Problema.
Eu preciso a principio criar 3 medidas separadas que calculem a média de volume de acordo com o tempo de contrato. Irei exemplifica-las abaixo.
Medida 1 - 6 Meses antes do contrato.
Esta medida deverá olhar meus clientes linha a linha e calcular a média mensal deles 6 meses antes do contrato ter iniciado.
Exemplo:
Se o meu cliente começou o contrato em jan/23 a média deve ser a soma do volume dos meses de jul/22 até dez/22 divido pela quantidade de meses disponíveis (com dados) neste período de 6 meses antes da data do contrato.
Este valor precisa estar somente na data de contrato jan/23
Medida 2 - 1 ano após a data do contrato.
Esta medida deverá olhar meus clientes linha a linha e calcular a média mensal deles 1 ano depois do contrato.
Exemplo:
Se o meu cliente começou o contrato em jan/23 a média deve ser a soma do volume dos meses de jan/23 até dez/23 divido pela quantidade de meses disponíveis (com dados) neste período de 12 meses após a data do contrato.
Este valor precisa estar somente na data de contrato jan/23
Medida 3 - 2 anos após a data do contrato.
Esta medida deverá olhar meus clientes linha a linha e calcular a média mensal deles 1 ano depois do aniversário de 1 ano do contrato.
Se o meu cliente começou o contrato em jan/23 a média deve ser a soma do volume dos meses de jan/24 até dez/24 divido pela quantidade de meses disponíveis (com dados) neste período de 12 meses após a data do contrato de 1 ano.
Este valor precisa estar somente na data de contrato jan/23

Detalhe: Esta medida precisa funcionar também quando colocar apenas o Vendedor no contexto de filtro, Supervisor, Filial, e etc.
Ela não pode ser restrita apenas ao contexto de filtro do COD CLIENTE SISTEMA 1

Minha solução meio errada.

6 Meses antes (Auxiliar) =

VAR vDatesinperiod7M = DATESINPERIOD(dCalendario[Date], MAX(dCalendario[Date]), -7, MONTH)

VAR vDatesinperiod1M = DATESINPERIOD(dCalendario[Date],MAX(dCalendario[Date]), -1, MONTH)

VAR vResult =

(

    CALCULATE(

        [Volume (#)],

        vDatesinperiod7M

    )

    -

    CALCULATE(

        [Volume (#)],

        vDatesinperiod1M

    )

)

VAR vDivisao =

DIVIDE(

    vResult,

    [Contagem 6M (#)],

    0

)

RETURN

vDivisao
 

 

Medida Final =

CALCULATE(

    [A0 (Auxiliar)],

    TREATAS(

        CALCULATETABLE(

            VALUES(fContratos[INICIO])

        ),

        dCalendario[Inicio do Mês]

    )

)

Está solução funciona individualmente quando filtro apenas um código, porém quando olho conforme a tabela abaixo ele calcula errado para os clientes, não sei o que pode ser e o que fiz para tentar consertar embora correto apenas piorou a performance do relatório e quebrou diversas visualizações que estavam com pouquissimos dados nela.

Segue em anexo o arquivo xlsx, o pbix e a imagem de onde quero que essa medida vá.
Agradeço antecipadamente a ajuda!

pbix_forum_duvida.pbix base_duvida_forum.xlsx

[Erick Oliveira]

Boa noite, @Lucas Denker;

Use as medidas:

6 Months = 
SUMX (
    dCliente,
    VAR _StartofContract = CALCULATE ( MAX ( dContratos[INICIO CONTRATO] ) )
    VAR _Months = 
        CALCULATETABLE (
            WINDOW  (
                -7, REL,
                -1, REL,
                ALL ( dCalendario[AnoMês] ),
                ORDERBY ( [AnoMês], ASC )
            ),
            KEEPFILTERS ( dCalendario[Data] = _StartofContract )
        )
    RETURN
        AVERAGEX ( _Months, [Volume] )
)

1 Months = 
SUMX (
    dCliente,
    VAR _StartofContract = CALCULATE ( MAX ( dContratos[INICIO CONTRATO] ) )
    VAR _Months = 
        CALCULATETABLE (
            WINDOW  (
                0, REL,
                11, REL,
                ALL ( dCalendario[AnoMês] ),
                ORDERBY ( [AnoMês], ASC )
            ),
            KEEPFILTERS ( dCalendario[Data] = _StartofContract )
        )
    RETURN
        AVERAGEX ( _Months, [Volume] )
)

2 Months = 
SUMX (
    dCliente,
    VAR _StartofContract = CALCULATE ( MAX ( dContratos[INICIO CONTRATO] ) )
    VAR _Months = 
        CALCULATETABLE (
            WINDOW  (
                12, REL,
                23, REL,
                ALL ( dCalendario[AnoMês] ),
                ORDERBY ( [AnoMês], ASC )
            ),
            KEEPFILTERS ( dCalendario[Data] = _StartofContract )
        )
    RETURN
        AVERAGEX ( _Months, [Volume] )
)

 

[Lucas Denker]

Excelente funcionou perfeitamente! Muito obrigado! Gostaria de entender um pouco melhor a razão de ter colocado as variáveis dentro do sumx, não estou acostumado a escrever DAX assim então não sei bem o que ele realiza por trás fazendo desta forma. Se puder me explicar pra eu entender melhor

[Erick Oliveira]

Ao usar a variável iterando sobre a tabela dClientes, eu consigo armazenar valores de cada cliente durante a iteração. Exemplo: _StartofContract, cada cliente possui uma data de inicio, ao usar a variável dentro do SUMX, eu consigo capturar e armazenar o inicio de cada cliente.

[Lucas Denker]

Preciso de uma outra ajuda com relação a essa medida...

Eu criei uma versão parecida a ela com base no que você me mandou, porém quando eu filtro algum ano ele esta fazendo apenas para os dados filtrados e não sei como fazer para tirar a influencia do ano dentro da tabela feita pelo calculatetable.

Minha medida é essa abaixo:
O que está acontecendo é que caso eu tenho um cliente que teve seu contrato iniciado em jan/24 e filtro 2024 essa medida não calcula nada pra ele por que ela está filtrando a tabela do window para ter apenas os dados de 2024 e não pega o período completo do contrato 6 meses atrás, ou seja desde jul/22 até dez/22. 
Sei que já dei essa pergunta como resolvida, mas poderia me ajudar novamente por favor?

6 Months =

SUMX (

    VALUES(dContratos[COD Cliente]),

    VAR _StartofContract = CALCULATE ( MAX ( dContratos[INICIO] ) )

    /* Calcula a data de início do contrato mais recente para cada cliente */

    VAR _Months =

        CALCULATETABLE (

            WINDOW  (

                -6, REL,

                -1, REL,

                ALL ( dCalendario[Inicio do Mês] ),

                ORDERBY ( dCalendario[Inicio do Mês], ASC )

            ),

            KEEPFILTERS ( dCalendario[Inicio do Mês] = _StartofContract )

        )

    /* Define uma janela de 6 meses, do 6º mês anterior ao mês anterior ao início do contrato para cada cliente */

    VAR _Volume =

        CALCULATE(

            [Volume (#)],

            _Months

        )

    /* Calcula o volume total durante esses 6 meses */

    RETURN

    DIVIDE(_Volume, [Contagem 6 Meses (#)])

)