Страницы: 1 2 След.
RSS
Цепной расчет значений внутри столбца в PowerBI
 
Есть два вопроса, один "базовый", второй на основе ответа - практический.
Имеется динамика данных (прошедшие периоды + прогноз на N периодов) по остаткам (только за прошедшие периоды), продажам (факт прошедших периодов и прогноз на будущие), приходам (будущие периоды).
Подскажите, как реализовать расчет значения остатков по формуле Остатки периода N = Остатки предыдущего периода - продажи текущего + приходы текущего.
Попытался начать решение расчетным столбцом (в приложении файл pbix).
Он складывает как нужно, но он складывает и историю остатков. Необходимо куда то внедрить условие, что если в столбце остатки существует значение, то столбце прогноз остатков отображается оно, без выполнения складывания и вычитания. И только на будущие периоды нужен цепной расчет.
Скрытый текст

Следующий вопрос будет: как рассчитать такие значения для базы данных с множеством товаров, т.е. еще и реализовать фильтр по разным ТМЦ, а не сквозное цепное сложение по всей базе данных. Если такое реализуемо.
Изменено: zrbite - 18.09.2018 12:20:37
 
Вам нужны функции CALCULATE, ALLEXCEPT и EARLIER. К сожалению ничего больше подсказать не могу ибо ваше вложение нечем открыть. Поищите в поиске по форуму эти функции это будут примерные пути решения вашей задачи.
Изменено: PooHkrd - 18.09.2018 12:22:40
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Поищите в поиске по форуму эти функции это будут примерные пути решения вашей задачи.
Привет, Алексей.
Не весёлые там пути задачи. Вместо того, чтобы иметь стандартную таблицу. Начальный остаток, приход, расход. В этой таблице там где есть остаток, нет данных по приходу. В результате вместо банальной задачи к текущей дате посчитать сумму приходов минус сумма расходов плюс начальный остаток, начинаются танцы с бубном. Я в этом деле, ну никак не специалист, но так и не понимаю, для чего люди стремятся каждый раз изобретать велосипед.
Изменено: Андрей VG - 18.09.2018 16:28:58
 
Цитата
Андрей VG написал:
В результате вместо банальной задачи к текущей дате посчитать сумму приходов минус сумма расходов плюс начальный остаток,
так подскажите как. Сумма приходов за прошлые периоды можно сказать отсутствует - слишком неудобно ее получить. Сумма исходящего остатка на каждый период не всегда будет равна сумме учитываемых приходов, расходов и остатку на начало периода, т.к. есть "уходимость" в побочные виды списания остатков, которые нет смысла учитывать.
Но если можно посчитать итоговый входящий остаток на начало прогнозного периода, то можно за него принять последнее значение в прошедшем периоде?
 
Цитата
zrbite написал:
Но если можно посчитать итоговый входящий остаток на начало прогнозного периода, то можно за него принять последнее значение в прошедшем периоде?
Вот собственно то, что и должно было, по идее, быть вопросом вашей темы и её заголовком ;)  Осталось, собственно, соединить с классическим вычислением остатка. Как-то так, по идее, можно довести до ума. Глядишь, завтра или Максим или Степан зайдут в тему, да представят блестящее решение.
Код
Остаточки =
VAR lastRemainderDate =
    CALCULATE (
        MAX ( 'Таблица1'[Дата] );
        FILTER ( 'Таблица1'; NOT ( ISBLANK ( 'Таблица1'[Остатки] ) ) )
    )
VAR lastRemainder =
    CALCULATE (
        SUM ( 'Таблица1'[Остатки] );
        FILTER ( 'Таблица1'; 'Таблица1'[Дата] = lastRemainderDate )
    )
VAR calcRemainder =
    lastRemainder
        + SUMX (
            FILTER (
                'Таблица1';
                'Таблица1'[Дата] <= EARLIER ( 'Таблица1'[Дата] )
                    && 'Таблица1'[Дата] > lastRemainderDate
            );
            'Таблица1'[Приходы] - 'Таблица1'[Продажи]
        )
RETURN
    IF ( ISBLANK ( 'Таблица1'[Остатки] ); calcRemainder; 'Таблица1'[Остатки] )

Успехов.
P. S. Один вопрос - одна тема.
Изменено: Андрей VG - 18.09.2018 20:55:09
 
Цитата
Андрей VG написал:
Глядишь, завтра или Максим или Степан зайдут в тему,
Приветствую, Андрей!
К сожалению, сейчас я больше тут читатель, чем "решатель" - другой город, другая работа, где пока совсем нет времени и хватает своих интересных задач, а с андроида много не напишешь.
К ТС дата начального остатка всегда одна? Я имею ввиду, что например в учетной системе на конец месяца или наоборот начало месяца фиксируется какой то остаток, соответственно на даты в середине месяца остаток расчетный?
 
Я в свое время боролся с такой задачей - расчёт прогнозного остатка с учетом приходов. Задачка оказалась весьма нетривиальной, но мне помог её решить один очень крутой но очень скромный товарищ Owen Auger
Описание задачки и решение тут (на буржуинском). Там в сообщении отмеченном как ответ есть ссылка на файл - если она уже не жива - я попробую поискать файл у себя, но без гарантий
Изменено: Максим Зеленский - 20.09.2018 12:08:50
F1 творит чудеса
 
Максим, спасибо за ссылку. Каюсь, просмотрел бегло, но как-то не увидел алгоритмических различий в подходе, или я что-то упускаю?. К сожалению, сам файл к скачиванию не доступен :( , чтобы посмотреть разницу в реализации кода.
 
У меня скачался.
Я, если честно, не возвращался с того времени к этому вопросу, поэтому не могу быстро прокомментировать
F1 творит чудеса
 
Цитата
StepanWolkoff написал:
Я имею ввиду, что например в учетной системе на конец месяца или наоборот начало месяца фиксируется какой то остаток, соответственно на даты в середине месяца остаток расчетный?
В идеале я беру месячный остаток = средний остаток по неделям месяца. Но для упрощения скорее всего буду брать дискретный остаток на месяц = остаток на 1-е число месяца. Поэтому дат середины месяца в файле не будет. Каждый месяц = 1-е число этого месяца.
 
Цитата
Максим Зеленский написал:
Описание задачки и решение  тут (на буржуинском) .
Максим Зеленский,
Спасибо! Файл скачал, посмотрел. Думаю, надо реально оценивать возможности - пока это слишком высокий уровень для меня, боюсь даже если разберусь с логикой, запомнить что либо для меня сейчас не получится. Вариант Андрея пока видится более наглядным, хотя так же вызывает трудности в освоении)
 
Цитата
zrbite написал:
Максим Зеленский , Спасибо!
Цитата
zrbite написал:
Вариант Андрея пока видится более наглядным, хотя так же вызывает трудности в освоении)
Я так полагаю, что из-за трудностей с освоением я от вас спасибо и не дождусь?  :D
 
Цитата
zrbite написал: Сумма исходящего остатка на каждый период не всегда будет равна сумме учитываемых приходов, расходов и остатку на начало периода, т.к. есть "уходимость" в побочные виды списания остатков, которые нет смысла учитывать.
а то, что с периодом времени они у вас накапливаются и (неучтённые) вносят большую ошибку в итоги, - вас это не смущает ?  8) своеобразный у вас учёт...  
Изменено: JeyCi - 23.09.2018 11:27:51
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
вопрос на уточнение появился:
как PowerPivot и PowerBI понимают, какой тип данных им брать из исходной таблицы ??.. - вот в такой ситуации как у ТС - когда есть в Остатках и null и числа... - PP мне говорит, что у него текст... есть ли способ ему сказать, что он не прав?.. и в PowerBI с этим какая-то др. ситуация или он тоже автоматом не распознаёт числа, если в столбце есть и null'и?..
p.s.
PQ так использует #ChangedType... а этим товарищам как объяснять, что столбец цифровой??.. хотела поковырять задачу TC'a в PP- задумалась над этим нюансом уже с самого начала...  
Изменено: JeyCi - 23.09.2018 11:27:09
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
JeyCi, перед загрузкой в модель укажите в скрипте тип столбца number или int64, если указан тип any, то Power pivot будет воспринимать его как текстовый. Если же вы загрузите данные в модель из таблицы, то его тип можно менять прямо в модели, есть там такие кнопки.
Изменено: PooHkrd - 23.09.2018 20:30:30
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал: в скрипте тип столбца number или int64
это в смысле в PQ?.. я брала просто с листа - у меня excel2013 PP
Цитата
PooHkrd написал: Если же вы загрузите данные в модель из таблицы, то его тип можно менять прямо в модели, есть там такие кнопки.
честно говоря не вижу... ок доберусь до компа - буду искать... Спасибо
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
буду искать...
Там не долго искать:
Вот горшок пустой, он предмет простой...
 
PooHkrd - спасибо!! и я увидела благодаря!! вашему скрину - просто с ноута - слепая иногда... увидела и в своём 2013 - всё ок!
Изменено: JeyCi - 25.09.2018 10:27:48
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Через эту же менюшку можно также задавать формат отображения в сводной таблице для мер. Очень удобная штука - указал в рублях и табличке все как надо показывается.
Вот горшок пустой, он предмет простой...
 
ок - учту... посматриваю иногда на этого зверя - 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 с ТипамиДанных
Изменено: JeyCi - 26.09.2018 15:05:36
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Ух ты, VAR ... RETURN работает в экселевском PP? Не знал, думал только в PBI. JeyCi, спасибо за открытие.
Изменено: PooHkrd - 26.09.2018 14:59:13
Вот горшок пустой, он предмет простой...
 
Цитата
Андрей VG написал: спасибо и не дождусь?  
и Андрей VG спасибо!
Изменено: JeyCi - 26.09.2018 15:01:56
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
PooHkrd написал:
Ух ты, VAR ... RETURN
Это только в 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...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
PQ-надстройку для 2016 не нашла..
А зачем? Он же в него встроен на ленте "Данные", раздел "Скачать & преобразовать" Или я чего-то не так понял?
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Он же в него встроен на ленте "Данные", раздел "Скачать & преобразовать
ой как они его встроили, спрятали... так сразу и не сказала бы и не подумала бы  ...
PooHkrd спасибо! вот теперь я точно готова к знакомству с прогрессом
Изменено: JeyCi - 28.09.2018 09:47:37
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
к #1
Цитата
т.е. еще и реализовать фильтр по разным ТМЦ, а не сквозное цепное сложение по всей базе данных
... допустим зоомагазин...
"Вертолётики" по каждой ТМЦ - в развёрнутом виде - (нединамические - можно только вынести в фильтр ТМЦ - но обязательно выбрать Одну, чтобы получить корректный разворот всего "вертолётика" по данной ТМЦ)
===
по-хорошему и более интересно, может быть:
надо выделить в отдельные таблицы Измерения: ТМЦ и Календарь... [кстати у меня не пролазил такой полный вид на форум >100кб]...
И создавать из таблицы_Фактов список ТМЦ с финальным остатком на последнюю Дату, чтобы проваливаясь в Детализацию можно было каждый "вертолётик" увидеть в полный рост...
ИЛИ делать вариант динамический для Дат_Календаря... (меня смущает, что, проданный и уже не поступающий товар, может зависнуть баластом в Своде - нужна будет отдельная фильтрация, чтобы убрать висяки - перегружающая память)
ТАКЖЕ если анализировать Даты помесячно - то, полагаю, вообще можно использовать удобную функцию DAX OPENBALANCEMONTH... вместо EARLIER
p.s.
функция EARLIER оказалась не очень удобной, т.к., видимо, привязана к контексту Начальной таблицы-Данных... либо изначально в Модели настраивать нужную Сортировку [через таблицы(измерения)-со столбцом последовательность-сортировки, далее используя RELATED]... чтобы EARLIER заработало "comme il faut" - мне так показалось...
p.p.s.
вобщем набросала компактненький вариант (для только имеющихся Дат) - когда Продажи и Поступления редки :) ... чтобы не подряжать в расчёт каждый месяц календаря... а поковырять только то, что есть
(уж очень захотелось поразглядывать каждый "вертолётик".. да ещё на новом языке)
жёлто-чёрное - вычисляемое поле, жёлто-красные - меры... на заре знакомства с DAX (ещё даже не multidimensional-modelling в полном смысле этого слова)...
всем спаибо за интересный пример!.. упрощать на свой вкус и цвет...
(столбец CHECK - подтверждает корректность расчётов)
Изменено: JeyCi - 08.10.2018 17:27:04
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
перестаралась в последнем посту - наставила много минусов (которые друг на друга выходят в плюс):
поубирать все минуса - будет логичнее с точки зрения бух. логики и восприятия глазом...
т.е.
Мера 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  :oops: )
Изменено: JeyCi - 09.10.2018 08:58:28
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
довела до толку:
- поправила бух логику (всё на плюса, кроме крЕдита)
- вывела Вычисляемый столбец в Меру (так логичнее исходя из смысла расчётов её)
- привела в порядок Итоги (исправления в Итогах меры lastRes - и остальные авто-нормализуются)
- выставила Срезы (по ТМЦ - удобно, по Дате - для эксперимента ;))
=> и вуаля... [даже самой понравилось] - если понравится что-то ещё - поправлю...
p.s.
на больших объёмах не проверяла... если будет тормозить - то лучше, наверно, делать правильно - выделяя Измерения в отдельные таблицы (и плясать от них)... на досуге
p.p.s.
DAX 2016 показался достаточно удобным для вычисления предыдущих значений и куммулятивного счёта...
на выстраивание иерархий ещё не тестировала этот язык...
также в отличие от SQL - удобна независимость по отношению к местоположению файла
p.p.p.s.
а РЫБ получилось больше всех на балансе у Зоомагазина  :) (точнее его прогнозе)
Изменено: JeyCi - 10.10.2018 11:15:22
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Страницы: 1 2 След.
Наверх