Estou tentando descobrir a expressão DAX [para MS PowerPivot no Excel 2010] para distribuir uniformemente a soma de um valor por todo o intervalo ao qual é aplicada e resumir novamente por um determinado período / período. É trivial a aplicação cruzada no SQL Server, embora a cada tentativa, o resultado seja o mesmo.

Eu tenho a saída do MS Project salva como Excel e importada / transformada usando o PowerQuery, portanto, as datas de início e término / término são datas apropriadas, {X} he {Y} d são números inteiros e a duração do dia do calendário entre eles já estão calculados / formatados para o modelo. Eu também tenho uma tabela de datas criada que tem as datas contíguas da primeira data até a última e uma tabela de anos que possui a representação de string dos anos de 4 dígitos pelos quais quero resumir.

O modelo tem a seguinte aparência:

Criei colunas calculadas nas tabelas ResourceQuery, TaskQuery e AssignmentQuery (todas obtidas diretamente da saída do MS Project) e no ServiceAreaQuery (valores exclusivos do TaskQuery ... essencialmente subprojetos). Cada um também possui uma medida simples que é a soma da coluna Horas atribuídas.

Os dados em si parecem com o que você esperaria de um arquivo do Project 2010, e têm {start_date}, {finish_date} e horas. As datas de uma tarefa podem variar de 1 dia a 5 anos ... e é aí que reside o meu problema.

Como faço para dividir / dividir o valor pré-somado em tarefas de longa execução para corresponder ao intervalo de tempo que estou procurando?

Mesmo se eu usar a coluna do ano da tabela de datas, a inteligência do tempo não a capturará e estou ficando sem ideias para umaCALCULAR (SOMA (FILTRO (CONTRATAÇÕES (DATAS ENTRE)))) tipo de coisa.

Existem duas etapas intermediárias que tentei descobrir sem sucesso. Imagino que ambos sejam resolvidos pela mesma função eficaz para chegar aometa final de horas, por área de serviço, por recurso, por ano.

Tabela dinâmica para mostrar

Horas por recurso, por anoHoras por área de serviço, por ano

para mostrar o objetivo final de

Horas, por área de serviço, por recurso, por ano

Você pode ver o problema na saída abaixo.

Observe que ao usar o total de horas atribuídas e o nome do recurso de AssignmentQuery, recebo as somas corretas, mas ao usar qualquer valor de data ... só recebo as horas na data de início (o relacionamento ativo no modelo). O que eu preciso é que essas horas sejam distribuídas uniformemente pelo período em que são aplicáveis (por isso, se algo tiver 1.000 horas entre 1/1/16 e 1/1/19, eu esperaria que 333 horas / ano aparecessem) .

Meu pensamento inicial é que a função seletor / filtro / cálculo precisa fazer o seguinte:

Selecione as horas para a pessoaSelecione os dias no período filtrado para (por exemplo, mês / ano / trimestre / o que for) de um filtro ou como um cabeçalho de colunaCalcular as horas por diaObter os dias úteis no período filtradoselecione a soma das horas da sobreposição

Todas as idéias são muito apreciadas! Estou aberto a fazer alguma criação adicional de ETL / dados como uma etapa do PowerQuery, mas realmente gostaria de descobrir a expressão DAX correta para isso, para que possa estar disponível como um filtro de tempo / filtro no projeto.

Desde já, obrigado.

** Edite para publicar a versão revisada da resposta fornecida **

[Hours Apportioned Raw] :=
DIVIDE (
    CALCULATE (
                [Hours],
                FILTER (
                    AssignmentQuery,
                    AssignmentQuery[Start_Date] <= MAX ( Dates[Date] )
                        && AssignmentQuery[Finish_Date] >= MAX ( Dates[Date] )
                        )
                )
    , ( COUNTROWS (
                    DATESBETWEEN ( 
                                    Dates[Date]
                                    , FIRSTDATE ( AssignmentQuery[Start_Date] )
                                    , LASTDATE ( AssignmentQuery[Finish_Date] )
                                 )
                  )
      )
)