Страницы: 1
RSS
DAX. Расчет общей стоимости аренды за период с разбивкой по месяцам
 
Добрый вечер уважаемые форумчане!
Есть данные об аренде автомобилей - стоимость аренды в месяц и период использования. Необходимо найти общую стоимость аренды всех авто в каждом месяце используя Power Query и меры DAX в Power Pivot. Массивными формулами я посчитала, но руководство настаивает на "пивоте")
Подскажите, пожалуйста, в какую сторону копать и возможно ли это реализовать без 100500 допстолбцов и прочей мишуры))
Изменено: Настя_Nastya - 11.01.2022 21:34:54
 
Настя_Nastya, так как в PP не осилю, сделал в PQ
 
Михаил Л, спасибо большое, но хотелось бы знать - можно ли решить задачу именно в пивоте)
Изменено: Настя_Nastya - 12.01.2022 12:54:57
 
Цитата
Настя_Nastya написал:
решить задачу именно в пивоте)
Так и мне это охота увидеть)
По любому выпрямлять в PQ
 
Настя_Nastya, как стоимость месячной аренды перекладывать на дни, по какому алгоритму? Кол-во дней в месяцах различны, а периоды аренды авто указаны в пересекающихся месяцах.
П.С. просто фактическое кол-во дней аренды делим от дней всего в месяце?
Изменено: Vladimir Chebykin - 12.01.2022 13:15:50
 
Vladimir Chebykin, нужно для каждого месяца найти стоимость аренды авто в день (разделить на кол-во дней в месяце), а потом, умножть на кол-во дней аренды..
Формулами я находила стоимость аренды в первый месяц (стоим.аренды делила на общ.кол-во дней в месяце и умножала на колво дней аренды), потом прибавляла кол-во месяцев, кроме первого и последнего в периоде аренды, умноженных на стоимость аренды, а затем находила стоимость в последний месяцмесяц аренды.
В том то и основная загвоздка, что в каджом месяце стоимость аренды за день разная
 
Цитата
Vladimir Chebykin написал:
Кол-во дней в месяцах различны,
Да, при неполном месяце аренды делить на фактическое количество дней аренды. Например, по отдельной строке в феврале на скрине
Изменено: Михаил Л - 12.01.2022 14:14:41
 
Вариант на DAX во вложении. Данные немного расходятся, но я не вникал в Ваши формулы массива и не совсем понял Ваше пояснение. Объясните мне на пальцах - как по машине "10008" в Москве за декабрь получилось  15468,75? Я беру аренду за месяц,  делю на кол-во календарных дней и умножаю на фактич. кол-во дней аренды, т.е. 55000/31*9 =15967,74. Либо у Вас ошибка, либо я алгоритм не допонял.
П.С. по машинке "10006" в феврале проверьте результат. По-моему у Вас там считается как високосный февраль, а мы же про 2021-ый год рассматриваем, верно?
П.П.С. в-общем, мой результат идентичен решению Михаил Л,
П.П.П.С.
Цитата
Михаил Л написал:
По любому выпрямлять в PQ
Михаил Л, что Вы имели ввиду, не понял посыл?
Изменено: Vladimir Chebykin - 12.01.2022 14:16:23
 
Цитата
Vladimir Chebykin написал:
не понял посыл?
так вы и без этого посчитали
 
Цитата
Vladimir Chebykin написал:
что Вы имели ввиду, не понял посыл?
Что ваше решение верное, но на мой взгляд это тоже извращение, и лучше уж выпрямить в плоскую таблицу, пусть даже с детализацией до месяцев и потом простыми функциями DAX это все анализировать без многоэтажных конструкций.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
лучше уж выпрямить в плоскую таблицу
согласен, если речь идет о локальной задаче. Если требуется решение в какой-либо сложной модели данных, то вариант с выпрямлением скорее всего отпадет, потому что тут уже на стороне ETL начнется изврат))
Изменено: Vladimir Chebykin - 12.01.2022 14:37:00
 
Цитата
Vladimir Chebykin написал:
как по машине "10008" в Москве за декабрь получилось  15468,75?
да, здесь небольшая ошибка в формуле, она же и некорректно сработала по февралю и остальным (делила на кол-во дней в месяце+1).
Vladimir Chebykin, Спасибо большое!
PooHkrd, спасибо)
Изменено: Настя_Nastya - 12.01.2022 15:04:46
 
Еще вариант в одной мере. Делал на модели Vladimir Chebykin.
Код
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...). Главное - получить скалярное значение. А значение там для каждой строки всего одно :) .
Изменено: surkenny - 12.01.2022 16:06:47
 
Цитата
MAX (                        0;                        1 + MIN ( MAX ( 'Calendar'[Date] ); MAX ( 'Данные'[окончание аренды] ) )                            - MAX ( MIN ( 'Calendar'[Date] ); MAX ( 'Данные'[начало аренды] ) )                    )
Добрый вечер! подскажите пожалуйста, почему не использовали:
Код
DATEDIFF([начало аренды];[окончание аренды];DAY)+1 

..... это что то связано с производительностью или корректностью вычисления кол-во дней
 
Цитата
azma написал:
почему не использовали
Может в файле покажите?
 
Цитата
azma написал:
почему не использовали:DATEDIFF([начало аренды];[окончание аренды];DAY)+1
потому что тогда проще [начало аренды] - [окончание аренды] + 1
Но в данном случае для каждого месяца разная дневная стоимость аренды. И получить нужный результат немного сложнее :)
Нам необходимо вычислить часть, попадающую в конкретный месяц.
Изменено: surkenny - 12.01.2022 16:10:16
 
А есть ли вариант мерами попроще для таблицы запроса Данные2 ?
 
Цитата
Михаил Л написал:
для таблицы запроса Данные2 ?
Можно так:
Код
Стоимость:=SUMX('Данные2'; [стоимость аренды в месяц]/DAY(EOMONTH([деньаренды];0)) )
Вот горшок пустой, он предмет простой...
 
PooHkrd, спасибо!
Не зарываясь в PQ и PP. Проще наверное некуда)
 
Цитата
Михаил Л написал:
Проще наверное некуда)
Именно про это выше и писал. Если такая гранулярность не нужна, то можно до месяцев схлопнуть посчитав количество дней аренды. Будет моделька полегче и мера почти такая же.
Вот горшок пустой, он предмет простой...
 
Моя мера и мера Vladimir Chebykin некорректно считали при детализации глубже месяца. Подправил меру (в модель добавил номер недели для примера).
Код
rent amount :=
VAR startDate =
    MIN ( 'Calendar'[Date] )
VAR endDate =
    MAX ( 'Calendar'[Date] )
VAR result =
    SUMX (
        VALUES ( 'Calendar'[YYYY-MM] );
        VAR rentTable =
            ADDCOLUMNS (
                'Данные';
                "@rentAmount";
                    CALCULATE (
                        MAX (
                            0;
                            1
                                + MIN (
                                    MIN ( endDate; MAX ( 'Calendar'[Date] ) );
                                    MAX ( 'Данные'[окончание аренды] )
                                )
                                - MAX (
                                    MAX ( startDate; MIN ( 'Calendar'[Date] ) );
                                    MAX ( 'Данные'[начало аренды] )
                                )
                        )
                            * MIN ( 'Данные'[стоимость аренды в месяц] )
                            / COUNTROWS (
                                FILTER ( ALL ( 'Calendar' ); 'Calendar'[YYYY-MM] = MIN ( 'Calendar'[YYYY-MM] ) )
                            )
                    )
            )
        VAR rentAmount =
            SUMX ( rentTable; [@rentAmount] )
        RETURN
            IF ( rentAmount > 0; rentAmount )
    )
RETURN
    result
Изменено: surkenny - 12.01.2022 18:30:45
 
Цитата
PooHkrd написал:
лучше уж выпрямить в плоскую таблицу, пусть даже с детализацией до месяцев и потом простыми функциями DAX
Верно. Но так не интересно :)
Вариант "нормального" решения (детализация до дня):
PQ:
Скрытый текст

PP:
Скрытый текст

PooHkrd, случаем не подскажете, можно ли избежать шага typed2?
 
Цитата
surkenny написал:
можно ли избежать шага typed2?
Если брать ваш вариант то можно так:
Скрытый текст

Так-то, если ради искусства, то можно и сразу таблицу собрать  :D
Скрытый текст
Изменено: PooHkrd - 13.01.2022 10:30:56
Вот горшок пустой, он предмет простой...
 
PooHkrd, благодарю. Я понимаю, как это сделать предложенными Вами способами, но для большинства форумчан это, наверное, излишнее усложнение и главное - не ведет к увеличению производительности.
Я скорее немного о другом :) Если таблицу с заданными типами преобразовать в список списков значений столбцов Table.ToColumns ( tbl ), то при обратной операции Table.FromColumns ( lst ) тип данных сохранится.
В данном случае у значений внутри списков списка тип date. А при развертывании неопределенный :( И у Table.ExpandListColumn() аргумента для типа нет :(
 
Цитата
surkenny написал:
И у Table.ExpandListColumn() аргумента для типа нет
Именно поэтому так и усложнил, т.к. Table.ExpandTableColumn() дает возможность наследования типов столбцов из родительских таблиц. Вам шашечки или ехать?  :D
Вот горшок пустой, он предмет простой...
 
Цитата
surkenny написал:
А при развертывании неопределенный  И у Table.ExpandListColumn() аргумента для типа нет
Берем вчерашний пост Максима и вперед:
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "Данные" ]}[Content],
  typed = Table.TransformColumnTypes ( src, { { "начало аренды", type date }, { "окончание аренды", type date } } ),
  addDatesLst = Table.AddColumn (
    typed,
    "дата",
    each List.Dates ( [начало аренды], Duration.TotalDays ( [окончание аренды] - [начало аренды] ) + 1, #duration ( 1, 0, 0, 0 ) ),
    type {date}
  ),
  delClmns = Table.RemoveColumns ( addDatesLst, { "начало аренды", "окончание аренды" } ),
  expandDates = Table.ExpandListColumn ( delClmns, "дата" ),
  addDayCost = Table.AddColumn ( expandDates, "стоиомость аренды в день", each [стоимость аренды в месяц] / Date.DaysInMonth ( [дата] ), type number ),
  dekClmns2 = Table.RemoveColumns ( addDayCost, { "стоимость аренды в месяц" } )
in
  dekClmns2
Вот горшок пустой, он предмет простой...
 
PooHkrd, спасибо огромное :)
Страницы: 1
Наверх