Есть два вопроса, один "базовый", второй на основе ответа - практический. Имеется динамика данных (прошедшие периоды + прогноз на N периодов) по остаткам (только за прошедшие периоды), продажам (факт прошедших периодов и прогноз на будущие), приходам (будущие периоды). Подскажите, как реализовать расчет значения остатков по формуле Остатки периода N = Остатки предыдущего периода - продажи текущего + приходы текущего. Попытался начать решение расчетным столбцом (в приложении файл pbix). Он складывает как нужно, но он складывает и историю остатков. Необходимо куда то внедрить условие, что если в столбце остатки существует значение, то столбце прогноз остатков отображается оно, без выполнения складывания и вычитания. И только на будущие периоды нужен цепной расчет.
Скрытый текст
Прогноз Остатков = VAR Date1 = 'Таблица1'[Дата] Return SUMX(FILTER('Таблица1';'Таблица1'[Дата]<=Date1);'Таблица1'[Остатки]-'Таблица1'[Продажи]+'Таблица1'[Приходы])
Следующий вопрос будет: как рассчитать такие значения для базы данных с множеством товаров, т.е. еще и реализовать фильтр по разным ТМЦ, а не сквозное цепное сложение по всей базе данных. Если такое реализуемо.
Вам нужны функции CALCULATE, ALLEXCEPT и EARLIER. К сожалению ничего больше подсказать не могу ибо ваше вложение нечем открыть. Поищите в поиске по форуму эти функции это будут примерные пути решения вашей задачи.
PooHkrd написал: Поищите в поиске по форуму эти функции это будут примерные пути решения вашей задачи.
Привет, Алексей. Не весёлые там пути задачи. Вместо того, чтобы иметь стандартную таблицу. Начальный остаток, приход, расход. В этой таблице там где есть остаток, нет данных по приходу. В результате вместо банальной задачи к текущей дате посчитать сумму приходов минус сумма расходов плюс начальный остаток, начинаются танцы с бубном. Я в этом деле, ну никак не специалист, но так и не понимаю, для чего люди стремятся каждый раз изобретать велосипед.
Андрей VG написал: В результате вместо банальной задачи к текущей дате посчитать сумму приходов минус сумма расходов плюс начальный остаток,
так подскажите как. Сумма приходов за прошлые периоды можно сказать отсутствует - слишком неудобно ее получить. Сумма исходящего остатка на каждый период не всегда будет равна сумме учитываемых приходов, расходов и остатку на начало периода, т.к. есть "уходимость" в побочные виды списания остатков, которые нет смысла учитывать. Но если можно посчитать итоговый входящий остаток на начало прогнозного периода, то можно за него принять последнее значение в прошедшем периоде?
zrbite написал: Но если можно посчитать итоговый входящий остаток на начало прогнозного периода, то можно за него принять последнее значение в прошедшем периоде?
Вот собственно то, что и должно было, по идее, быть вопросом вашей темы и её заголовком Осталось, собственно, соединить с классическим вычислением остатка. Как-то так, по идее, можно довести до ума. Глядишь, завтра или Максим или Степан зайдут в тему, да представят блестящее решение.
Андрей VG написал: Глядишь, завтра или Максим или Степан зайдут в тему,
Приветствую, Андрей! К сожалению, сейчас я больше тут читатель, чем "решатель" - другой город, другая работа, где пока совсем нет времени и хватает своих интересных задач, а с андроида много не напишешь. К ТС дата начального остатка всегда одна? Я имею ввиду, что например в учетной системе на конец месяца или наоборот начало месяца фиксируется какой то остаток, соответственно на даты в середине месяца остаток расчетный?
Я в свое время боролся с такой задачей - расчёт прогнозного остатка с учетом приходов. Задачка оказалась весьма нетривиальной, но мне помог её решить один очень крутой но очень скромный товарищ Owen Auger Описание задачки и решение тут (на буржуинском). Там в сообщении отмеченном как ответ есть ссылка на файл - если она уже не жива - я попробую поискать файл у себя, но без гарантий
Максим, спасибо за ссылку. Каюсь, просмотрел бегло, но как-то не увидел алгоритмических различий в подходе, или я что-то упускаю?. К сожалению, сам файл к скачиванию не доступен , чтобы посмотреть разницу в реализации кода.
StepanWolkoff написал: Я имею ввиду, что например в учетной системе на конец месяца или наоборот начало месяца фиксируется какой то остаток, соответственно на даты в середине месяца остаток расчетный?
В идеале я беру месячный остаток = средний остаток по неделям месяца. Но для упрощения скорее всего буду брать дискретный остаток на месяц = остаток на 1-е число месяца. Поэтому дат середины месяца в файле не будет. Каждый месяц = 1-е число этого месяца.
Максим Зеленский написал: Описание задачки и решение тут (на буржуинском) .
Максим Зеленский, Спасибо! Файл скачал, посмотрел. Думаю, надо реально оценивать возможности - пока это слишком высокий уровень для меня, боюсь даже если разберусь с логикой, запомнить что либо для меня сейчас не получится. Вариант Андрея пока видится более наглядным, хотя так же вызывает трудности в освоении)
zrbite написал: Сумма исходящего остатка на каждый период не всегда будет равна сумме учитываемых приходов, расходов и остатку на начало периода, т.к. есть "уходимость" в побочные виды списания остатков, которые нет смысла учитывать.
а то, что с периодом времени они у вас накапливаются и (неучтённые) вносят большую ошибку в итоги, - вас это не смущает ? своеобразный у вас учёт...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
вопрос на уточнение появился: как PowerPivot и PowerBI понимают, какой тип данных им брать из исходной таблицы ??.. - вот в такой ситуации как у ТС - когда есть в Остатках и null и числа... - PP мне говорит, что у него текст... есть ли способ ему сказать, что он не прав?.. и в PowerBI с этим какая-то др. ситуация или он тоже автоматом не распознаёт числа, если в столбце есть и null'и?.. p.s. PQ так использует #ChangedType... а этим товарищам как объяснять, что столбец цифровой??.. хотела поковырять задачу TC'a в PP- задумалась над этим нюансом уже с самого начала...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
JeyCi, перед загрузкой в модель укажите в скрипте тип столбца number или int64, если указан тип any, то Power pivot будет воспринимать его как текстовый. Если же вы загрузите данные в модель из таблицы, то его тип можно менять прямо в модели, есть там такие кнопки.
PooHkrd написал: в скрипте тип столбца number или int64
это в смысле в PQ?.. я брала просто с листа - у меня excel2013 PP
Цитата
PooHkrd написал: Если же вы загрузите данные в модель из таблицы, то его тип можно менять прямо в модели, есть там такие кнопки.
честно говоря не вижу... ок доберусь до компа - буду искать... Спасибо
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Через эту же менюшку можно также задавать формат отображения в сводной таблице для мер. Очень удобная штука - указал в рублях и табличке все как надо показывается.
ок - учту... посматриваю иногда на этого зверя - PP... но пока на вы с ним (не испытывала потребностей в нём - всё как-то малой кровью спасалась - без этого монстра)... но надо же быть в курсе прогресса... спасибо за ликбез!!
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
всё-таки установила 2016 (со встроeнным PP) PP->Управление->Вставить из буфера обмена таблицу (или Подключение к др книге)->столбец Остаточки от Андрей VG проходят... потом вставка Сводной на листе с источником данного Подключения... получилось, как во вложении... НО при попытке использовать саму таблица (хоть с листа, хоть из др.книги) в качестве Источника для модели данных - даже кнопка PP-ТипДанных не помогла Приход превратить в числа!!?? (хоть Остатки исходной таблицы вроде уже и числами стали - после того, как на листе формат ячеек заменила, а может и оттого, что 2016)... может у меня сам PP какой старенький в 2016 (раз с конвертацией Прихода в число не справляется)... наверно нужны какие обновления... p.s. 2013 выдаёт ошибку ТипаДанных даже на Остатках 2010 надстройку пробовала установить - очень подвешивает excel при открытии -- вот такие Выводы у меня пока -- похоже нужен нормальный полноценный 2016 -- потому что модель данных на data из буфера обмена, не имея связи с источником, даже при выгрузке в Сводную - даёт удобство не comme il faut - т.е. никакого... вобщем Подключения и ТипыДанных хромают на заре юности PP... обновляться придётся, когда руки дойдут, полагаю p.p.s там во вложении, начиная со строки с пометкой жёлтым - все: +Приход-Продано=ИтогОстаточки совпадает... до пометки жёлтым остатки, видимо введённые руками прежние (с учётом "внебалансовых" расходов/приходов, как писал ТС, полагаю, или с учётом расхода/прихода неучтённого малого, который учитывается в конце фискального года, наверно) - поэтому не проверяла (есть небольшие расхождения на глаз с остатками ведёнными вручную ТСом - до жёлтой черты)... удивляться не надо ... меня больше удивило поведение PP с ТипамиДанных
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Это только в 2016/365, плюс некоторый набор функций DAX в 2016 доступен. Если открывать в 2013, то будут проблемы. хотя 2013 и 2016 по структуре совместимы.
всё-таки сделала 2013 - через PQ Connection only (чтобы побороть форматы - по-другому не получилось, а сохранить связь с источником - это как раз comme ils faut) - Подключение к этому Connection в PP-Управление - VAR...RETURN работает только с 2016 похоже -- поэтому все промежуточные расчёты в Модели в доп.столбцах -- что не очень memory-efficient, it seems... - выгрузка в Плоскую Сводную Таблицу - из Модели p.s. формулами от жёлтой черты проверила Сальдо_Прогнозное -- всё совпадает сделано только по идеям от Андрей VG p.p.s. PQ-надстройку для 2016 не нашла... они наверно и не делали её для 2016 (т.к. в system requirements указаны только 2010 и 2013 для pq-add-in)... наверно, потому что [Excel2016 (with PP) + PQ = PowerBI]... жаль что расширения его файлов не универсальны для открытия в самом excel...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
PooHkrd написал: Он же в него встроен на ленте "Данные", раздел "Скачать & преобразовать
ой как они его встроили, спрятали... так сразу и не сказала бы и не подумала бы ... PooHkrd спасибо! вот теперь я точно готова к знакомству с прогрессом
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
т.е. еще и реализовать фильтр по разным ТМЦ, а не сквозное цепное сложение по всей базе данных
... допустим зоомагазин... "Вертолётики" по каждой ТМЦ - в развёрнутом виде - (нединамические - можно только вынести в фильтр ТМЦ - но обязательно выбрать Одну, чтобы получить корректный разворот всего "вертолётика" по данной ТМЦ) === по-хорошему и более интересно, может быть: надо выделить в отдельные таблицы Измерения: ТМЦ и Календарь... [кстати у меня не пролазил такой полный вид на форум >100кб]... И создавать из таблицы_Фактов список ТМЦ с финальным остатком на последнюю Дату, чтобы проваливаясь в Детализацию можно было каждый "вертолётик" увидеть в полный рост... ИЛИ делать вариант динамический для Дат_Календаря... (меня смущает, что, проданный и уже не поступающий товар, может зависнуть баластом в Своде - нужна будет отдельная фильтрация, чтобы убрать висяки - перегружающая память) ТАКЖЕ если анализировать Даты помесячно - то, полагаю, вообще можно использовать удобную функцию DAX OPENBALANCEMONTH... вместо EARLIER p.s. функция EARLIER оказалась не очень удобной, т.к., видимо, привязана к контексту Начальной таблицы-Данных... либо изначально в Модели настраивать нужную Сортировку [через таблицы(измерения)-со столбцом последовательность-сортировки, далее используя RELATED]... чтобы EARLIER заработало "comme il faut" - мне так показалось... p.p.s. вобщем набросала компактненький вариант (для только имеющихся Дат) - когда Продажи и Поступления редки ... чтобы не подряжать в расчёт каждый месяц календаря... а поковырять только то, что есть (уж очень захотелось поразглядывать каждый "вертолётик".. да ещё на новом языке) жёлто-чёрное - вычисляемое поле, жёлто-красные - меры... на заре знакомства с DAX (ещё даже не multidimensional-modelling в полном смысле этого слова)... всем спаибо за интересный пример!.. упрощать на свой вкус и цвет... (столбец CHECK - подтверждает корректность расчётов)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
перестаралась в последнем посту - наставила много минусов (которые друг на друга выходят в плюс): поубирать все минуса - будет логичнее с точки зрения бух. логики и восприятия глазом... т.е. Мера CUMM_from_Blank_Saldo: =... RETURN (debitRate-creditRate), Мера Прогноз: =CALCULATE(MAX(Database[lastRes])+[CUMM_from_Blank_Saldo]), Мера All_Together: =VAR calc=CALCULATE(MAX(Database[lastRes])+[CUMM_from_Blank_Saldo]) ... p.s. файл сделан на xl_2016 и почему-то выдаёт ошибку при обновлении/открытии Модели в xl_2013 - из-за VAR...RETURN (которого нет в 2013 )
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
довела до толку: - поправила бух логику (всё на плюса, кроме крЕдита) - вывела Вычисляемый столбец в Меру (так логичнее исходя из смысла расчётов её) - привела в порядок Итоги (исправления в Итогах меры lastRes - и остальные авто-нормализуются) - выставила Срезы (по ТМЦ - удобно, по Дате - для эксперимента ) => и вуаля... [даже самой понравилось] - если понравится что-то ещё - поправлю... p.s. на больших объёмах не проверяла... если будет тормозить - то лучше, наверно, делать правильно - выделяя Измерения в отдельные таблицы (и плясать от них)... на досуге p.p.s. DAX 2016 показался достаточно удобным для вычисления предыдущих значений и куммулятивного счёта... на выстраивание иерархий ещё не тестировала этот язык... также в отличие от SQL - удобна независимость по отношению к местоположению файла p.p.p.s. а РЫБ получилось больше всех на балансе у Зоомагазина (точнее его прогнозе)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)