Я пытаюсь разработать выражение DAX [для MS PowerPivot в Excel 2010], чтобы равномерно распределить сумму значения по диапазону, к которому она применяется, и пересчитать ее за заданный промежуток времени / период. Перекрестное применение в SQL-сервере тривиально, хотя при каждой попытке получается один и тот же неправильный результат.

Я получил выходные данные из MS Project, сохраненные в виде Excel и импортированные / преобразованные с помощью PowerQuery, поэтому даты начала и окончания / окончания являются правильными датами, {X} h и {Y} d - целые числа, а продолжительность календарного дня между они уже рассчитаны / отформатированы для модели. У меня также есть созданная таблица дат, в которой есть непрерывные даты от первой до последней даты, а также таблица лет, которая имеет строковое представление четырехзначных лет, к которым я хочу подвести итог.

Модель выглядит так:

Я создал вычисляемые столбцы в таблицах ResourceQuery, TaskQuery и AssignmentQuery (все непосредственно взяты из выходных данных MS Project) и в ServiceAreaQuery (уникальные значения из TaskQuery… по существу, подпроекты). Каждый из них также имеет простую меру, которая является суммой столбца Назначенные часы.

Сами данные выглядят так, как вы ожидаете от файла Project 2010, и имеют {start_date}, {finish_date} и часы. Сроки выполнения задачи могут составлять от 1 дня до 5 лет ... и в этом моя проблема.

Как разделить / разделить предварительно суммированное значение для долго выполняющихся задач, чтобы соответствовать временному интервалу, который я ищу?

Даже если я использую столбец года из таблицы дат, сведения о времени не улавливают его, и у меня заканчиваются идеи дляРАСЧЕТ (SUM (FILTER (COUNTROWS (DATESBETWEEN)))) Тип вещи.

Есть два промежуточных шага, которые я пытался понять безрезультатно. Я полагаю, что они оба решаются одной и той же эффективной функцией, чтобы добраться доконечная цель часов, по области обслуживания, по ресурсам, по годам.

Сводная таблица, чтобы показать

Часы по ресурсам, по годамЧасы по зонам обслуживания, по годам

чтобы показать конечную цель

Часы, по зонам обслуживания, по ресурсам, по годам

Вы можете увидеть проблему в выходных данных ниже.

Обратите внимание, что при использовании общего количества назначенных часов и имени ресурса из AssignmentQuery я получаю правильные суммы, но при использовании любого значения даты… я получаю только часы относительно даты начала (активные отношения в модели). Мне нужно, чтобы эти часы были равномерно распределены по периоду, к которому они применимы (поэтому, если что-то будет иметь 1000 часов в период с 01.01.16 по 01.01.19, я бы ожидал, что будет показано 333 часа в год) ,

Сначала я подумал, что функция селектора / фильтра / вычисления должна выполнять следующие действия:

Выберите часы для человекаВыберите дни в фильтруемом периоде (например, месяц / год / квартал / что угодно) из фильтра или заголовка столбца.Рассчитать часы в деньПолучить рабочие дни в отфильтрованном периодевыберите сумму часов из перекрытия

Любые идеи с благодарностью! Я открыт для создания дополнительных ETL / данных в качестве шага PowerQuery, но очень хотел бы выяснить правильное DAX-выражение для этого, чтобы оно могло быть чем-то доступным в качестве среза / фильтра времени в проекте.

Заранее спасибо.

** Редактировать, чтобы опубликовать исправленную версию предоставленного ответа **

[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] )
                                 )
                  )
      )
)