Добрый вечер уважаемые форумчане! Есть данные об аренде автомобилей - стоимость аренды в месяц и период использования. Необходимо найти общую стоимость аренды всех авто в каждом месяце используя Power Query и меры DAX в Power Pivot. Массивными формулами я посчитала, но руководство настаивает на "пивоте") Подскажите, пожалуйста, в какую сторону копать и возможно ли это реализовать без 100500 допстолбцов и прочей мишуры))
Настя_Nastya, как стоимость месячной аренды перекладывать на дни, по какому алгоритму? Кол-во дней в месяцах различны, а периоды аренды авто указаны в пересекающихся месяцах. П.С. просто фактическое кол-во дней аренды делим от дней всего в месяце?
Vladimir Chebykin, нужно для каждого месяца найти стоимость аренды авто в день (разделить на кол-во дней в месяце), а потом, умножть на кол-во дней аренды.. Формулами я находила стоимость аренды в первый месяц (стоим.аренды делила на общ.кол-во дней в месяце и умножала на колво дней аренды), потом прибавляла кол-во месяцев, кроме первого и последнего в периоде аренды, умноженных на стоимость аренды, а затем находила стоимость в последний месяцмесяц аренды. В том то и основная загвоздка, что в каджом месяце стоимость аренды за день разная
Вариант на DAX во вложении. Данные немного расходятся, но я не вникал в Ваши формулы массива и не совсем понял Ваше пояснение. Объясните мне на пальцах - как по машине "10008" в Москве за декабрь получилось 15468,75? Я беру аренду за месяц, делю на кол-во календарных дней и умножаю на фактич. кол-во дней аренды, т.е. 55000/31*9 =15967,74. Либо у Вас ошибка, либо я алгоритм не допонял. П.С. по машинке "10006" в феврале проверьте результат. По-моему у Вас там считается как високосный февраль, а мы же про 2021-ый год рассматриваем, верно? П.П.С. в-общем, мой результат идентичен решению Михаил Л, П.П.П.С.
Что ваше решение верное, но на мой взгляд это тоже извращение, и лучше уж выпрямить в плоскую таблицу, пусть даже с детализацией до месяцев и потом простыми функциями DAX это все анализировать без многоэтажных конструкций.
PooHkrd написал: лучше уж выпрямить в плоскую таблицу
согласен, если речь идет о локальной задаче. Если требуется решение в какой-либо сложной модели данных, то вариант с выпрямлением скорее всего отпадет, потому что тут уже на стороне ETL начнется изврат))
Vladimir Chebykin написал: как по машине "10008" в Москве за декабрь получилось 15468,75?
да, здесь небольшая ошибка в формуле, она же и некорректно сработала по февралю и остальным (делила на кол-во дней в месяце+1). Vladimir Chebykin, Спасибо большое! PooHkrd, спасибо)
rent amount :=
SUMX (
VALUES ( 'Calendar'[YYYY-MM] );
VAR rentTable =
ADDCOLUMNS (
'Данные';
"@rentAmount";
CALCULATE (
MAX (
0;
1 + MIN ( MAX ( 'Calendar'[Date] ); MAX ( 'Данные'[окончание аренды] ) )
- MAX ( MIN ( 'Calendar'[Date] ); MAX ( 'Данные'[начало аренды] ) )
)
* MIN ( 'Данные'[стоимость аренды в месяц] )
/ COUNTROWS ( 'Calendar' )
)
)
VAR rentAmount =
SUMX ( rentTable; [@rentAmount] )
RETURN
IF ( rentAmount > 0; rentAmount )
)
* у окончания/начала аренды и стоимости в месяц все равно какую функцию использовать (min, max, values...). Главное - получить скалярное значение. А значение там для каждой строки всего одно .
MAX ( 0; 1 + MIN ( MAX ( 'Calendar'[Date] ); MAX ( 'Данные'[окончание аренды] ) ) - MAX ( MIN ( 'Calendar'[Date] ); MAX ( 'Данные'[начало аренды] ) ) )
Добрый вечер! подскажите пожалуйста, почему не использовали:
azma написал: почему не использовали:DATEDIFF([начало аренды];[окончание аренды];DAY)+1
потому что тогда проще [начало аренды] - [окончание аренды] + 1 Но в данном случае для каждого месяца разная дневная стоимость аренды. И получить нужный результат немного сложнее Нам необходимо вычислить часть, попадающую в конкретный месяц.
Именно про это выше и писал. Если такая гранулярность не нужна, то можно до месяцев схлопнуть посчитав количество дней аренды. Будет моделька полегче и мера почти такая же.
let
src = Excel.CurrentWorkbook(){[ Name = "Данные" ]}[Content],
typed = Table.TransformColumnTypes ( src, { { "начало аренды", type date }, { "окончание аренды", type date } } ),
addDatesLst = Table.AddColumn (
typed,
"дата",
each Table.FromColumns( {List.Dates ( [начало аренды], Duration.TotalDays ( [окончание аренды] - [начало аренды] ) + 1, #duration ( 1, 0, 0, 0 ) )},type table [дата=date]),
type table [дата=date]
),
Expanded = Table.ExpandTableColumn(addDatesLst, "дата", {"дата"}, {"дата"}),
delClmns = Table.RemoveColumns ( Expanded, { "начало аренды", "окончание аренды" } ),
addDayCost = Table.AddColumn ( delClmns, "стоиомость аренды в день", each [стоимость аренды в месяц] / Date.DaysInMonth ( [дата] ), type number ),
dekClmns2 = Table.RemoveColumns ( addDayCost, { "стоимость аренды в месяц" } )
in
dekClmns2
Так-то, если ради искусства, то можно и сразу таблицу собрать
Скрытый текст
Код
let
Source = Excel.CurrentWorkbook(){[Name="Данные"]}[Content],
AddedTabs = Table.AddColumn(
Source,
"tab",
each let
l=Record.ToList(_),
d=List.Transform({Int64.From(l{3})..Int64.From(l{4})}, Date.From)
in Table.FromColumns({{l{0}},{l{1}},List.Transform(d, each l{2}/Date.DaysInMonth(_))}&{d},{"Город", "№ авто", "стоимость аренды в день", "дата"}),
type table
),
Combine = Table.Combine(AddedTabs[tab], type table [#"Город"=text, #"№ авто"=Int64.Type, #"стоимость аренды в день"=number, #"дата"=date]),
FilledDown = Table.FillDown(Combine,{"Город", "№ авто"})
in
FilledDown
PooHkrd, благодарю. Я понимаю, как это сделать предложенными Вами способами, но для большинства форумчан это, наверное, излишнее усложнение и главное - не ведет к увеличению производительности. Я скорее немного о другом Если таблицу с заданными типами преобразовать в список списков значений столбцов Table.ToColumns ( tbl ), то при обратной операции Table.FromColumns ( lst ) тип данных сохранится. В данном случае у значений внутри списков списка тип date. А при развертывании неопределенный И у Table.ExpandListColumn() аргумента для типа нет
surkenny написал: И у Table.ExpandListColumn() аргумента для типа нет
Именно поэтому так и усложнил, т.к. Table.ExpandTableColumn() дает возможность наследования типов столбцов из родительских таблиц. Вам шашечки или ехать?