Страницы: 1
RSS
DAX (Power BI) : Добавить себестоимость в таблицу продаж согласно дате поступления
 
Всем привет Коллеги!
Недавно открыл мир Power BI, пытаюсь рассчитать себестоимость товара, но не могу сообразить последний шаг.
Суть задачи проста, есть две таблицы ПродажиРегистрНакопления и СебестоимостьНоменклатуры. Создал столбец Себестоимость (в таблице ПродажиРегистрНакопления) и пытаюсь подтянуть цену себестоимости из таблицы СебестоимостьНоменклатуры, согласно дате поставки. Но с одним условием, что дата поставки должна быть ближайшая (в меньшую сторону) от даты продажи.
У меня получилось написать фильтр, который отбирает ближайшую дату поставки из таблицы СебестоимостьНоменклатуры, но я не могу найти какую функцию применить, чтобы подтянуть цену из этой отфильтрованной таблицы согласно дате поставки.
Пример вложил.

Рабочий фильтр:
Код
ДатаСебестоимость = 
CALCULATE(
    LASTNONBLANK('СебестоимостьНоменклатуры'[Период];1);
    FILTER(
        'СебестоимостьНоменклатуры';
        'ПродажиРегистрНакопления'[Номенклатура] = 'СебестоимостьНоменклатуры'[Номенклатура] &&
        'ПродажиРегистрНакопления'[Период] >= 'СебестоимостьНоменклатуры'[Период]
        )
)
 
Формула для вычисляемого столбца в таблице ПродажиРегистрНакопления
Код
Себестоимость последней партии =
CALCULATE (
    MAX ( 'СебестоимостьНоменклатуры'[Цена] );
    CALCULATETABLE (
        LASTDATE ( 'СебестоимостьНоменклатуры'[Период] );
        FILTER (
            'СебестоимостьНоменклатуры';
            'СебестоимостьНоменклатуры'[Период]
                <= EARLIER ( 'ПродажиРегистрНакопления'[Период] )
                && 'СебестоимостьНоменклатуры'[Номенклатура]
                    = EARLIER ( 'ПродажиРегистрНакопления'[Номенклатура] )
        )
    )
)
F1 творит чудеса
 
Максим Зеленский,
не работает конструкция, выдает ошибку: "В вызове функции "LASTDATE" указан столбец даты, содержащий повторяющиеся значения даты. Это не поддерживается."
 
Цитата
kiber написал:
выдает ошибку: "В вызове функции "LASTDATE" указан столбец даты, содержащий повторяющиеся значения даты.
Попробуйте так:
Код
LASTDATE ( VALUES( 'СебестоимостьНоменклатуры'[Период] ) )
Вот горшок пустой, он предмет простой...
 
PooHkrd,
Неа, та же ошибка
Изменено: kiber - 03.12.2018 15:10:34
 
kiber, кстати, а где у вас находится столбец 'СебестоимостьНоменклатуры'[Номенклатура]? Ошибки нет в примере?
Вот горшок пустой, он предмет простой...
 
не нужно вставлять в цитату копию предыдущего сообщеия [МОДЕРАТОР]

Да, спасибо, что указали на ошибку, это ошибка в примере, забыл указать.
Корректный файл прикрепил.
 
В общем так. В файле формула чудесно работает без изменений. Проблема была в отсутствующем столбце. Вы бы все таки давали пример, который не нужно переделывать, с уже готовой моделью и т.д. А то желание и время за вами переделывать редко у кого имеется. Вы так всех помогающих распугаете.
Вот горшок пустой, он предмет простой...
 
PooHkrd, а как мне посмотреть код в этом файле? Мне надо его вставить в Power BI, в Экселе с DAX я еще не работал :)
 
В файле формула из поста №2 от Максима Зеленского. Какой еще код интересует? Чтобы зайти в модель данных нужно иметь Excel 2013-2016 с подключенной надстройкой PowerPivot и нажать на зеленую кнопку на скрине.
Изменено: PooHkrd - 03.12.2018 16:26:34
Вот горшок пустой, он предмет простой...
 
PooHkrd, формулу которую привел Максим Зеленский я проверял не в файле примера, а на своей реальной модели данных и там столбец Номенклатура всегда была, но как раз на ней формула и выдавала ошибку, о том, что "В вызове функции "LASTDATE" указан столбец даты, содержащий повторяющиеся значения даты" . Т.е. функции LASTDATE не нравится повторяющиеся значения даты и отсутствие столбца "Номенклатура" в файле примера тут никак не связано с этой ошибкой.
Я еще раз проверил эту формулу, но результат ошибки тот же.
 
Выкладывайте свой .pbix с теми же табличками.
Изменено: PooHkrd - 03.12.2018 16:56:35
Вот горшок пустой, он предмет простой...
 
PooHkrd, Данных очень много, несколько миллионов строк, да и тут данные по продажам, постараюсь слепить эту же модель на тестовых обезличенных данных и после этого смогу выложить.
 
Цитата
kiber написал:
Данных очень много,
Ну ё-мое.
Вот, все работает. Файл слепил из ваших же табличек за 2 минуты.
Изменено: PooHkrd - 03.12.2018 17:06:41
Вот горшок пустой, он предмет простой...
 
PooHkrd, Да, Вы правы, я сейчас тоже слепил из этих данных *.pbix и без ошибок формула отрабатывает. Но в реальной модели данных выдает ошибку. У меня используются связи между двумя этими таблицами по полю "Номенклатура" и это вся разница между файлом примера и реальными данными.
Ну что же, буду "копать" дальше, попытаюсь выяснить почему на реальных данных ругается функция "LASTDATE".
Спасибо большое за уделенное время! Теперь понятно хоть в какую сторону копать :)
 
PooHkrd, Я обезличил эти две таблицы, ошибка вся та же. Что-то тут не то, возможно в примере мало строк данных, а в реальной таблице этих повторов дат много, в чудеса я уже не верю :)
 
Выделите кусок данных строк на 100 с разными номенклатурами и кидайте как пример, лишь бы в 300 Кб уместиться. Посмотрим.
Вот горшок пустой, он предмет простой...
 
PooHkrd, выделил кусок в 30 строк, ошибка проявляется.
Файл во вложении.
Также приложил экселевские таблички как источник данных для этого файла.
Изменено: kiber - 03.12.2018 18:48:06
 
Цитата
kiber написал:
У меня используются связи между двумя этими таблицами по полю "Номенклатура" и это вся разница между файлом примера и реальными данными.
Это всё имеет значение! Это раз.
Во-вторых, может, у вас и таблица-календарь найдется? :)
В третьих, если вы решаете в PBI, это тоже надо писать, есть разница между возможностями DAX там и в Excel.
Открываю файл pbix - связь не вижу. Не, ну реально, издеваетесь?
Код
Себестоимость =
VAR LIFO_Date =
    CALCULATE (
        MAX ( 'СебестоимостьНоменклатуры'[Период] );
        FILTER (
            ALL (
                'СебестоимостьНоменклатуры'[Номенклатура];
                'СебестоимостьНоменклатуры'[Период]
            );
            'СебестоимостьНоменклатуры'[Период]
                <= EARLIER ( 'ПродажиРегистрНакопления'[Период] )
                && 'СебестоимостьНоменклатуры'[Номенклатура]
                    = EARLIER ( 'ПродажиРегистрНакопления'[Номенклатура] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'СебестоимостьНоменклатуры'[Цена] );
        'СебестоимостьНоменклатуры'[Период] = LIFO_Date
    )

Связи сами учитывайте, как они там настроены у вас - х.з. При наличии связи вторая часть фильтра может оказаться излишней.
F1 творит чудеса
 
Максим Зеленский, спасибо за код, но он ооочень тяжелый, съел всю оперативку (16Гб), влез в SWAP  и в конце концов Power BI выдало ошибку о нехватке памяти.
Логику я понял, попробую оптимизировать код.
Кстати на счет связей в файле примера, я специально их оттуда убрал, чтобы показать, что формула выдает ошибку совсем не из-за связей.
И на счет "В третьих, если вы решаете в PBI, это тоже надо писать" - это я тоже писал в заголовке темы, посмотрите внимательно.
 
Добрый день, спасибо всем кто помогает новичкам таким как я), у меня аналогичная задача один прям в один, только в связи с тем, что данных очень много я подсоединяюсь к базе SQL, она у нас под 1С, и что бы не грузить в модель данных такой объем информации, дабы не утяжелять файл, можно ли этот запрос реализовать в PQ? Так как новичок и только учусь, своим силами и мозгами не могу реализовать. Надеюсь это прочитают и помогут))  
Страницы: 1
Наверх