Страницы: 1
RSS
Нужна помощь для решения методами Power Pivot для расчета "рабочих" дней вагонов, временные функции DAX, две ключевые даты в строке данных
 

Добрый день.
Есть вагонный парк, вагоны которого колесят по разным маршрутам, перевозя те или иные грузы. Из журнала  можно вытащить номер вагона, дату прибытия на станцию погрузки, дату убытия со станции выгрузки. Этими датами в одной строке ограничиваются груженный (рабочий) пробег вагона.

Требуется расчет полезного времени вагона по месяцам, когда он был в работе. При этом некоторые рейсы могут начаться в одном месяце, а закончиться в следующем. Надо, чтобы "грузовые" дни попадали в соответствующий период. Простой пример для понимания сути - во вложении.

Делал два рабочих варианта:
1. в виде матрицы, в первом столбце были календарные дни по порядку, а в первой строке - номера вагонов. На пересечении формулы, которые тянут с листа с журналом данные и ставят единичку, если в этот день тот или иной вагон был в рейсе. Но вычисления при нескольких сотнях вагонов и в годовом периоде происходят прямо несколько минут.
2. попробовал заполнять эту же матрицу макросом, в котором во вложенных циклах прогоняются данные с журнала. Но на примере с небольшим временным диапазоном и даже с парой номеров вагонов стало ясно, что это не вариант - еще дольше, причем гораздо.

Есть ощущение, что в Power Pivot мерами, функциями DAX  будет гораздо быстрее. Но мозг себе сломал как это сделать, когда в данных есть две даты в одной записи и еще они могут в разные отчетные периоды попадать. Какие-то вещи, связанные с деньгами, количеством я еще могу сделать, но, когда с датами и временными диапазонами - не получается.

 
Доброе время суток.
Простейшее решение, создать столбец дат под загрузкой для каждого вагона.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Движение"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Номер вагона", Int64.Type}, {"Дата прибытия на погрузку", type date}, {"Пункт погрузки", type text}, {"Дата убытия после разгрузки", type date}, {"Пункт разгрузки", type text}}),
    addWayDateList = Table.AddColumn(Typed, "Дата под загрузкой", each List.Dates([Дата прибытия на погрузку], Duration.TotalDays([Дата убытия после разгрузки] - [Дата прибытия на погрузку]) + 1, #duration(1, 0, 0, 0))),
    expandWayList = Table.ExpandListColumn(addWayDateList, "Дата под загрузкой"),
    #"Changed Type" = Table.TransformColumnTypes(expandWayList,{{"Дата под загрузкой", type date}})
in
    #"Changed Type"
Потом просто соединяете по этому столбцу с таблицей календаря в Power Pivot и спокойно считаете.
 
Yerlan, в дополнение к решению от Андрей VG, если вагонов очень много и строчек получится много миллионов, то для оптимизации перед в загрузкой модель данные можно сгруппировать. Например, если в отчете данные требуются в разрезе месяцев, то для каждого вагона/ месяца сгруппировать данные с подсчетом количества дней. Тогда моделька будет просто летать.
P.S. и вам надо с вашими формулами определиться, в июле вы корректно посчитали, а в августе/сентябре как-то не очень - дни теряете.
Изменено: PooHkrd - 21.09.2020 09:53:16
Вот горшок пустой, он предмет простой...
 
Да, спасибо большое. Очень полезно.
Пытаюсь освоить DAX, а методы, которые кроются на уровне Power Query, блин... Надо серьезно поизучать.
 
Yerlan, DAX нужно применять к правильно организованным данным. У вас данные для DAX организованы не правильно. Значит их нужно преобразовать и потом уже производить анализ. Инструментом преобразования для Power Pivot служит Power Query. Так что в любом случае рекомендую начать с него. Тут у хозяина сайта и книжкаесть по адекватной цене, и приемов с использованием PQ уже вагон и маленькая тележка.
Изменено: PooHkrd - 21.09.2020 10:06:25
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Например, если в отчете данные требуются в разрезе месяцев
А далее захотят в разрезе недель или декад? А потом средняя количество вагонов по дням под погрузкой? Аппетит приходит во время еды...
Изменено: Андрей VG - 21.09.2020 10:46:01
 
Андрей VG, дык я ж не спорю. Но у меня есть разные файлики с разной детализацией, ибо 100 метровую дуру по которой рассчитываю всякое интересное в почту не протолкнешь и в эксель-онлайн она если и откроется (что не факт), то поворачивается с трудом. А сгруппированный файлик - ляпота.
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх