Страницы: 1
RSS
Dax Нарастающий итог с обнулением итогов после каждого нового значения
 
Добрый день коллеги!
Всех поздравляю с Новым годом!

Прошу по мере возможностей подсказать пути решения задачки:
Есть определённая таблица значений, выполняющая функцию некоторого условного выключателя, например - таблица окладов у кадровика, или таблица норм выдачи спецодежды у кладовщика.
Этот выключатель, может изменяться во времени. Задача - применять ту норму, которая по нему проходит до того момента пока не принята новая норма.
Например в январе норма была 5 морковок на человека, а в феврале норму хотим утвердить 6 морковок на человека, ну и так далее.
Так вот, хотелось бы получить таблицу с итогами которая бы отражала действие нормы в периоде, т.е. до февраля она бы была 5 а с февраля и дальше уже 6.

В голову приходят только мысли о том. что это можно сделать как-то нарастающим итогом, но ума не приложу как, ведь с каждой новой утвержденной нормой нарастающий итог будет увеличиваться. Т.е. я так понимаю что нужно какое-то обнуление на каждом шаге при установление новой нормы.
Таблица пример во вложении. Итоговый ожидаемый результат - также, помечено желтым цветом то, что хотелось бы видеть дополнительно.
Изменено: lostandleft - 14.01.2022 14:07:15
 
Код
мера = 
var last_day_update = 
CALCULATE(
   MAX('Движение'[ДатаОбновл]);
   FILTER(
      ALL('Calendar');
      'Calendar'[Date]<=MAX('Calendar'[Date])
   )
)
var rezult = 
CALCULATE(SUM([Значение]);'Calendar'[Date]=last_day_update)
return
rezult
 
, Круто, спасибо!
Не подумал что максимальное значение можно также как и сумму использовать.
Спасибо!
 
Еще вариант, чтобы для итогов тоже корректно считалось:
Код
мера2 :=
SUMX (
    CALCULATETABLE ( VALUES ( 'Движение'[ТМЦ] ); ALL ( 'Calendar' ) );
    CALCULATE (
        SUM ( 'Движение'[Значение] );
        LASTNONBLANK (
            FILTER ( ALL ( 'Calendar' ); 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) );
            COUNTROWS ( RELATEDTABLE ( 'Движение' ) )
        )
    )
)

P.S. lostandleft, к примеру, у томатов 02.01.2020 2 строки (со значениями 3 и 4). Вам точно сумма нужна? :)
Можно изменить на какую-то другую функцию:
SUM ( 'Движение'[Значение] ) / AVERAGE ( 'Движение'[Значение] ) / MIN ( 'Движение'[Значение] ) / MAX ( 'Движение'[Значение] )
Просто найти последнюю строку не получится. Только если изначально строки не проиндексировать в PQ.
 
,
Интересный вариант, разбираюсь, спасибо!
В PQ решать не вариант, хотелось именно на чистом DAX
Спасибо за Вариант!
 
lostandleft, я не предлагаю решать в PQ.
Если на одну дату у Вас несколько значений, то что должно быть в отчете? Последнее внесенное (то, что ниже в таблице)? Тогда одним DAX не обойдетесь.
Если это просто ошибка в примере, то используйте предложенные Вам меры спокойно :) SUM можно заменить на MIN, AVERAGE, MAX - результат не изменится. А лучше вообще заменить на VALUES. Тогда в случае нескольких строк для одного ТМЦ и одной даты будет ошибка.
Вариант Vladimir Chebykin корректен, если Вам не нужны итоги (сумма по всем ТМЦ на день, сумма за период и тд). Хотя и его не сложно переделать :)
Изменено: surkenny - 14.01.2022 18:43:03
 
Здравствуйте коллеги!
Решил обновить тему,
К сожалению в реальном кейсе как только дело коснулось изменения в периоде моя модель данных начала сбоить.
необходимо рассчитать итоги и среднее значение по имеющимся мерам.
Измененные вводные:
1 - Стоимость материалов используемых в производстве пирогов ежедневно может меняться. Стоимость - это цена продукта умноженное на его количество в расходе. В таблице приведена уже именно стоимость в каждом дне.
2 - Объем выпуска в деньл не однороден, выпусков может быть несколько в примере добавил таблицу - производство пирогов в день.
в рельном кейсе для каждой морковки, яблока и так далее предусмотрены дополнительные расходы связанные например с транспортировкой, упаковкой и прочим. Т.е. стоимость яблока составляет не только его цена на рынке, но также и его доставка и упаковка, и пошлины и так далее.
В реальном кейсе в PQ посчитал стоимость конкретного яблока с множив расход в день на норму расхода в день. Дополнительные расходы остались как есть отдельными сроками, их группировать к сожалению нельзя.

В итоге в рамках примера прошу помочь с расчетом итоговой стоимости пирогов за все время выпуска.
Пример итоговая таблица - отражены в файле примере.
С трудом уместил в архиве в 100 кб, приношу свои извинения если в РАР это кому-то не удобно.
почему-то мне не разрешено загружать в этой теме фаил большим размером.
Изменено: lostandleft - 31.03.2022 15:33:30
 
lostandleft, должны будете :) Работать будет корректно, даже если разная дата изменения цены для разных ингредиентов. Если в одну дату несколько цен на один ингредиент, возьмется максимальная (но такого не должно быть :) На этот случай можно в PQ отбирать, к примеру, последнее значение по времени на дату.
Для стоимости тоже некорректно умн
Код
Цена :=
VAR dataTbl =
    CROSSJOIN ( VALUES ( 'Движение'[ТМЦ] ); SUMMARIZE ( 'Пироги'; 'Пироги'[Дата] ) )
VAR allCost =
    SUMX (
        dataTbl;
        CALCULATE (
            VAR cnt =
                SUM ( 'Пироги'[Количество] )
            VAR curDate =
                CALCULATE ( MAX ( 'Пироги'[Дата] ) )
            VAR priceDate =
                CALCULATE (
                    MAX ( 'Движение'[ДатаОбновл] );
                    ALL ( 'Calendar' );
                    'Calendar'[Date] <= curDate
                )
            VAR price =
                CALCULATE (
                    MAX ( 'Движение'[Значение] );
                    ALL ( 'Calendar' );
                    'Calendar'[Date] = priceDate
                )
            VAR cost = cnt * price
            RETURN
                cost
        )
    )
VAR allCnt =
    SUM ( 'Пироги'[Количество] )
VAR result =
    DIVIDE ( allCost; allCnt )
RETURN
    result
 
1. Важное дополнение!!! Без изменения контекста фильтра по датам при выборе только тех дат, на которые нет данных в таблице Движение, мера выдаст пустое значение, так как на эту дату(-ы) таблица Движение пустая. Корректно: CALCULATETABLE ( VALUES ( 'Движение'[ТМЦ] ); ALL ( 'Calendar' ) ). В предыдущем примере, если в фильтре выбрать 6 января, будет пусто :(
2. Я бы все-таки считал стоимость этой мерой. А цену уже делением на количество. А то мы лишний раз делим в цене, а потом в стоимости умножаем:
Код
СтоимостьПирогов :=
VAR dataTbl =
    CROSSJOIN (
        CALCULATETABLE ( VALUES ( 'Движение'[ТМЦ] ); ALL ( 'Calendar' ) );
        SUMMARIZE ( 'Пироги'; 'Пироги'[Дата] )
    )
VAR allCost =
    SUMX (
        dataTbl;
        CALCULATE (
            VAR cnt =
                SUM ( 'Пироги'[Количество] )
            VAR curDate =
                MAX ( 'Пироги'[Дата] )
            VAR priceDate =
                CALCULATE (
                    MAX ( 'Движение'[ДатаОбновл] );
                    ALL ( 'Calendar' );
                    'Calendar'[Date] <= curDate
                )
            VAR price =
                CALCULATE (
                    MAX ( 'Движение'[Значение] );
                    ALL ( 'Calendar' );
                    'Calendar'[Date] = priceDate
                )
            VAR cost = cnt * price
            RETURN
                cost
        )
    )
RETURN
    allCost

Код
Цена:=DIVIDE ( [СтоимостьПирогов]; SUM ( 'Пироги'[Количество] ) )
Изменено: surkenny - 31.03.2022 16:53:30
 
Цитата
написал:
можно в PQ отбирать, к примеру, последнее значение по времени на дату.
Спасибо за ответ я именно так и делаю!
В PQ происходит отбор актуальности для позиций с ценами, как раз берется наибольшая цена.
Цитата
написал:
Я бы все-таки считал стоимость этой мерой. А цену уже делением на количество
Это безусловно правильный подход.
Я именно так и хотел бы это реализовать.

Еще, как правильно разобрать / прочитать эту меру?
Или, может быть подскажите как ее понять, может быть можно через ДаксСтудию как-то посмотреть поэтапно? Просто сам разобраться хочу.
Какая логика работы конструкции?
 
1. Берем уникальные значения дат из таблицы пирогов. В данном случае можно даже просто использовать VALUES ( 'Пироги'[Дата] ). Если же у нас бывают пироги с разной начинкой, то мы бы сгруппировали с помощью summarize ( 'Пироги', 'Пироги'[Дата], 'Пироги'[Начинка] ) или groupby. Теперь по задаче из примера нам нужно разделить каждую такую строку на 3 (для каждого ТМЦ. По сути для решения Вашего примера - получить декартово произведение двух таблиц. Это делаем с помощью crossjoin. Только таблицу значений ТМЦ нужно вычислить, сбросив фильтр по дате, так как себестоимость ТМЦ есть не на все даты. В итоге для мы "получили" таблицу дат (видимых в контексте всех фильтров) с разделением каждой строки на все уникальные значения ТМЦ.
2. Для каждой строки этой таблицы нам нужно посчитать стоимость. Нам известна дата и ТМЦ. Ищем себестоимость ТМЦ из строки на дату не позднее даты текущей строки. Так же в контексте строки (в чем отличие контекст строки от контекста фильтра и зачем нужно оборачивать наше выражение в calculate (спойлер - calculate инициализирует преобразование контекста строки в контекст фильтра), читаем у Феррари и Руссо). Так же для текущей строки считаем количество пирогов. Умножаем на себестоимость - получаем стоимость пирога.
3. Очевидно, что в строках сводной таблицы с фильтром по конкретному ТМЦ посчитается стоимость только по нему. CALCULATETABLE ( VALUES ( 'Движение'[ТМЦ] ); ALL ( 'Calendar' ) ) будет содержать только 1 строку, так как мы сбрасываем фильтр только по дате. Если в сводной включить итоговые результаты или убрать из строк ТМЦ, то стоимость посчитается по каждой дате для кадого ТМЦ и просуммируется.
Изменено: surkenny - 31.03.2022 19:39:50
 
Цитата
написал:
VALUES ( 'Движение'[ТМЦ] );
Могу ли я как-то отказаться от этой конструкции?
Повторюсь, в реальных данных я не могу использовать таблицу уникальных значений. По факту, яблок, много в таблице данных.
Т.е. графа яблоко - это некий условный ключ, но он не уникален. Есть вторая колонка которая дает дополнительную характеристику расходам по яблокам.
В реальных данных структура существенно сложнее чем в примере.
Данные примерные на картинке. в реальности еще немного сложнее.
Существует несколько справочников работающих с данными, справочник кодов наименований и справочник статей.
Итоговая сводная таблица берет данные из главных отдельных справочников и по ним уже строит показатели.
/, к сожалению не могу сформировать реальный пример, и по объему форум не пропускает, и по сложности задачи.

Нет ли другого подхода в решении, в принципе совсем другого подхода.
Как я понимаю логику Вашей меры, если я правильно понимаю ее.
1 - С помощью VALUES мы аналогично Distinkt создаем таблицу уникальных значений из одного столбца. Зачем мы добавляет фильтр ALL для дат не до конца понимаю. но видимо нужно чтобы список распространился на все даты. ОК.
2 - С помощью Summarize мы создаем еще одну таблицу данных просуммированное количество продаж/производства пирогов на каждую дату.
3 - С помощью кросджоина делаем третью таблицу cо всеми возможными вариантами рецептов на каждый день календаря.

а вот дальше не до конца понимаю конструкцию, вы не могли бы пояснить, если возможно
Код
SUMX (        dataTbl;
        CALCULATE ( 
//разные таблицы первичных данных
)
 
Пока писал сообщение увидел Ваш пост.
Спасибо за комментарии!
Изменено: lostandleft - 31.03.2022 19:50:57
Страницы: 1
Наверх