Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вопрос по формуле PowerPivot, Расчет отличия от вчерашнего дня
 
#1
21.02.2018 16:46:28
Коллеги, помогите. Обрабатываю массивы складских остатков. Мне доступны только артикул и сам остаток. Вся логика строится на отслеживании динамики пополнения склада и продаж.
В Excel cделал все просто замечательно. Начал переводить в PowerPivot логику расчетов и столкнулся с полным непониманием,  как реализовать расчет отличия от вчерашнего дня, при условии совпадения артикула.
Пример с массивом в файле. Последний столбик.  
 
 
 
#2
21.02.2018 17:35:24
Пример при помощи двух расчетных столбцов. Можно сделать и одной формулой, но у меня пока не получилось, там с контекстами засада какая-то.
Вот горшок пустой, он предмет простой...
 
 
 
#3
21.02.2018 18:20:32
При определении вчерашнего дня, Вы попытались использовать еще и фильтр с привязкой к артикулу. Но это работает только в самой верхней строке массива. А у меня 100 тыс строк. Т.е, между артикулами пустого поля с датой не создается. Соответственно все остальное тоже не работает
 
 
 
#4
21.02.2018 18:24:48
Доброе время суток.
Цитата
Алексей Рубцов написал:
Соответственно все остальное тоже не работает
Доказательство приложить сможете?
И лучше подобные вещи делать в Power Query, а не мучать DAX.
Изменено: Андрей VG - 21.02.2018 18:26:00
 
 
 
#5
21.02.2018 18:34:44
Целиком согласен по поводу Power Query. У меня эта штука великолепно работает в Excel. И вопросов не было, пока не случилась необходимость выложить в Excel Online. А там ограничение в 10 МБ. А у меня массив под 100 тыс строк. Я перевел уже все расчеты в PowerPivot, осталась вот эта мелочь. Об нее и споткнулся
 
 
 
#6
21.02.2018 18:36:12
Цитата
Алексей Рубцов написал:
Соответственно все остальное тоже не работает
Вы уверены?
Здесь все работало. А у вас не работает. И еще не понятна взаимосвязь, почему нельзя сделать этот столбец в PQ до помещения таблицы в модель данных? Как это связано с размером файла?
Изменено: PooHkrd - 21.02.2018 18:41:15
Вот горшок пустой, он предмет простой...
 
 
 
#7
21.02.2018 18:53:02
Попытался выложить доказательство, что не работает. Не могу. Формула, просто физически убивает файл. Модель перестает откликаться на нажатия клавиш. Блокируется сохранение. На скрине "Стобец 2" Это вчерашняя дата. При смене Артикула, первая запись дата должна быть пустой.
 
 
 
#8
21.02.2018 19:09:44
Цитата
Алексей Рубцов написал:
Формула, просто физически убивает файл
Вот именно поэтому лучше делать в Power Query, просто Join по двум версиям индексирования (от 0 и от 1) для подтаблицы группы уникального артикула.
 
 
 
#9
21.02.2018 19:24:58
Я возражу. У меня много рабочих проектов в PowerPivot. Но обратил внимание, что   ALLEXCEPT уложила уже не один проект и не один  ПК. А ПК у меня - зверски прокачанная рабочая станция. Ее уложить, это постараться надо. Я, похоже, нащупал решение. Выложу завтра, как только доработаю.  
 
 
 
#10
21.02.2018 22:59:21
Цитата
Алексей Рубцов написал:
Выложу завтра, как только доработаю.
Добро, посмотрим. Я давал совет, основываясь на собственном опыте. Но любая эмпирика - это не доказательство.
 
 
 
#11
22.02.2018 08:46:47
Я бы эту задачу решал с помощью мер, так, на мой взгляд, модель будет легче и быстрее. Создавать в модели вычисляемый столбец дат, да еще и чтобы там было значение Null - это вообще может выйти боком, тем более не понятно зачем он тут, кроме как для доп.вычислений.
Цитата
Алексей Рубцов написал:
В Excel cделал все просто замечательно
смотрю на файл пример - и вижу противоречие с тем, что вы хотите от PowerPivot. По логике формулы в Приход/Расход в Остатках у вас данные на конец дня, раз вы рассчитываете [Сегодняшний остаток]-[Вчерашний остаток], но на 21/06 у вас в поле Приход/Расход стоит 0, хотя должно быть 245, т.к. 20/06 по логике, которую вы требуете от PowerPivot у вас не было остатка или он был 0, раз этого числа нет, соответственно 245-0=245 ;)

ну и файл пример с мерами
Изменено: StepanWolkoff - 22.02.2018 08:48:28
 
 
 
#12
22.02.2018 09:05:35
Цитата
Алексей Рубцов написал:
На скрине "Стобец 2" Это вчерашняя дата. При смене Артикула, первая запись дата должна быть пустой.
Пожалуйста, вот она пустая при смене артикула.
И так и не стало понятно, что вам мешает всю эту бодягу провернуть в PQ перед загрузкой таблицы в модель данных? Зачем такие мучения?
И еще, за ради интереса что там такого страшного в ALLEXCEPT увеличил ваш пример до почти 1 млн строк и 62 тыс номенклатур.
Мой старенький нотбук с КорАй 3 и 8 Гб РАМ обновляет оба столбца секунд за 20. Что я делаю не так?
Изменено: PooHkrd - 22.02.2018 09:22:39
Вот горшок пустой, он предмет простой...
 
 
 
#13
22.02.2018 09:21:34
Цитата
PooHkrd написал:
И так и не стало понятно, что вам мешает всю эту бодягу провернуть в PQ перед загрузкой таблицы
мне вообще не понятно зачем всю эту бодягу тянуть в модель, тратя на это ресурсы: время обработки, память - когда все можно рассчитать в мерах))
 
 
 
#14
22.02.2018 09:26:46
Ну вот хочется человеку, наверное как-то использует дальше и по другому никак. А может как и я не все расчеты умеет решать при помощи мер. В принципе, какая разница, более одного решения уже предоставлено, рекомендаций тоже надавали. Думаю что все таки товарищу смогли помочь.
Степан, у меня в другом вопрос: можно ли два моих столбца объединить в одну формулу для одного столбца? Я пробовал, но фигня получается, не смог разобраться с контекстами. Заранее предупреждаю, что в моей версии Excel объявление VAR не работает(не дают ИТ мне свежих обновлений). Можно ли без VAR?
Изменено: PooHkrd - 22.02.2018 09:30:57
Вот горшок пустой, он предмет простой...
 
 
 
#15
22.02.2018 09:46:56
Цитата
PooHkrd написал:
можно ли два моих столбца объединить в одну формулу для одного столбца?
Код
=IF(
         ISBLANK(DATEADD('Таблица1'[Дата];-1;DAY));
         0;
         'Таблица1'[Остаток на складе]-
         CALCULATE(
                         CALCULATE(
                                       SUM('Таблица1'[Остаток на складе]);
                                       ALLEXCEPT('Таблица1';'Таблица1'[Артикул];'Таблица1'[Дата])
                                       );
                          DATEADD('Таблица1'[Дата];-1;DAY)
                          )
                  )
Изменено: StepanWolkoff - 22.02.2018 09:49:38 (добавил файл)
 
 
 
#16
22.02.2018 09:50:38
StepanWolkoff, спасибо! Поковыряюсь на досуге.
А вот это вот
Код
DATEADD('Таблица1'[Дата];-1;DAY)

я так понимаю можно использовать только в рамках данного примера?, .т.е. если будут пропуски в датах, то будет косяк?
Изменено: PooHkrd - 22.02.2018 09:52:06
Вот горшок пустой, он предмет простой...
 
 
 
#17
22.02.2018 09:58:16
PooHkrd, ну да, но мы же работаем с примером)) Естественно все эти моменты надо учитывать при проектировании модели, исходя из поставленных задач.
 
 
 
#18
22.02.2018 10:01:33
Вот именно, у меня же формулы в столбцах универсальные, и пропуски допускают, посему уточняющий вопрос, возможно ли без VAR реализовать такую же формулу, но в одном столбце? Но я не настаиваю.  :)
Изменено: PooHkrd - 22.02.2018 10:02:22
Вот горшок пустой, он предмет простой...
 
 
 
#19
22.02.2018 10:59:32
PooHkrd, коллега, вот объясните, что за интерес считать это именно в DAX?
 
 
 
#20
22.02.2018 11:11:37
Андрей VG, лично мне это для интереса, скажем так, я неоднократно на форуме натыкался на разные проблемы, которые мне вот прям сейчас не актуальны, но через пару-тройку месяцев глядь, а в загашнике уже и формула готовая есть  ;)  
Собственно потому и
Цитата
PooHkrd написал:
Но я не настаиваю.  
Изменено: PooHkrd - 22.02.2018 11:17:47
Вот горшок пустой, он предмет простой...
 
 
 
#21
22.02.2018 12:21:32
PooHkrd,

Код
[Остаток на складе]- SUMX (
        FILTER (
            ADDCOLUMNS (
                'Таблица1';
                "lastdate"; CALCULATE(MIN ([Дата]);
                    FILTER (
                        ALLEXCEPT ( 'Таблица1'; 'Таблица1'[Артикул] );
                        [Дата] > EARLIER ( [Дата] )
                    )
                )
            );
           [lastdate] = EARLIER ( [Дата] )
                && [Артикул] = EARLIER ( [Артикул] )
        );
        [Остаток на складе]
    )
 
 
 
#22
22.02.2018 12:31:53
Большое спасибо. Примерно по той же схеме пробовал 4 месяца назад, но не вышло. Теперь проверю где был не прав.
Вот горшок пустой, он предмет простой...
 
 
 
#23
22.02.2018 12:39:39
Цитата
PooHkrd написал:
Теперь проверю где был не прав
Скорее всего в [lastdate] = EARLIER ( [Дата] ). Т.к. наоборот [Дата] = EARLIER ( [lastdate] )сделать нельзя, потому что lastdate возникает позже, чем дата, а тут его надо вычислить вперед. Поэтому я и изменил подход, что ищем не предыдущую дату, а следующую.
 
 
 
#24
22.02.2018 12:59:00
Отвечу всем по порядку. Почему такие сложности? Проект с огромным числом  расчетов и очень большим массивом данных. Выложить надо в Excel Online. Там ограничение в 10МБ и прямая рекомендация использовать PowerPivot.
Обычный файл у меня раздулся до 80 мегабайт. Поэтому я решил все возможные расчеты убрать в модель.
Теперь о моем решении. Возможно, оно из каменного века, но я убрал циклы проверки списков, приведя все к проверке соседних строк. Выглядит это так:
ПолеФормула
СегодняИсход   2'[Дата]
Вчера'Исход   2'[Дата]-1
Вчерашний   остатокCALCULATE(sum('Исход   2'[кол-во]);FILTER('Исход 2';'Исход 2'[Дата]=EARLIER('Исход   2'[Вчера]));FILTER('Исход 2';'Исход 2'[Коды ТМЦ и   Дистрибьютора]=EARLIER('Исход 2'[Коды ТМЦ и Дистрибьютора])))
Коды ТМЦ и   ДистрибьютораConcatenate([Код   ТМЦ];Concatenate("_";[код Дистра]))
Отличие от   предыдущего дняif('Исход   2'[Вчерашний остаток]=0;0;CALCULATE(sum('Исход 2'[кол-во]);FILTER('Исход   2';'Исход 2'[Дата]=EARLIER('Исход 2'[Сегодня]));FILTER('Исход 2';'Исход   2'[Коды ТМЦ и Дистрибьютора]=EARLIER('Исход 2'[Коды ТМЦ и   Дистрибьютора])))-CALCULATE(sum('Исход 2'
 
 
 
#25
22.02.2018 13:00:52
=if('Исход 2'[Вчерашний остаток]=0;0;CALCULATE(sum('Исход 2'[кол-во]);FILTER('Исход 2';'Исход 2'[Дата]=EARLIER('Исход 2'[Сегодня]));FILTER('Исход 2';'Исход 2'[Коды ТМЦ и Дистрибьютора]=EARLIER('Исход 2'[Коды ТМЦ и Дистрибьютора])))-CALCULATE(sum('Исход 2'[кол-во]);FILTER('Исход 2';'Исход 2'[Дата]=EARLIER('Исход 2'[Вчера]));FILTER('Исход 2';'Исход 2'[Коды ТМЦ и Дистрибьютора]=EARLIER('Исход 2'[Коды ТМЦ и Дистрибьютора]))))
 
 
 
#26
22.02.2018 13:04:18
Вот, то что получилось
 
 
 
#27
22.02.2018 13:45:58
Алексей Рубцов, в сообщениях #24,#25 лучше код оформить с помощью кнопки <...>, посмотрите сообщения других участников.
Ну и главное что у вас работает то, что вы сделали и вас это устраивает, и уже второй вопрос - прислушаться к советам или нет.
 
 
 
#28
22.02.2018 14:35:49
Ок. Я переоформлю, но позже. Очень спешу с переделкой проекта. Его надо было сдать вчера утром. А я только сегодня нашел основное  решение.
 
 
 
Страницы: 1
Читают тему
Наверх