Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
dax нарастающим итогом со сдвигом дат
 
#1
07.10.2020 00:12:36
Доброго времени суток, форумчане! Возникла задача подсчета данных продаж нарастающим итогом за промежуток дат, но сдвинутых на год назад. По расчету по текущему промежутку вопросов нет, формулу создал -
Код
ВыручкаНИ = CALCULATE(SUM(Sale[Выручка]), FILTER(ALLSELECTED (Дата),Дата[Дата]<=max(Дата[Дата])))

Но попытка просчёта за этот же промежуток со сдвигом на год назад не получилась. Попытался применить DATEADD, на она не работает с max. Буду благодарен, если кто-то встречался с подобной задачей и даст рецепт её решения
Изменено: Sergey Chernichenko - 07.10.2020 07:51:06 (некорректное отображение формулы)
 
 
 
#2
07.10.2020 09:02:42
Sergey Chernichenko,
Она же вроде как нарастающая - год назад и так считает
 
 
 
#3
07.10.2020 12:04:42
Наверное я некорректно привел описание требуемого запроса. Необходимо, чтобы при выборе промежутка дат формула считала выручку за такой же промежуток только прошлого года. Например, если пользователь выбрал промежуток дат с 15 августа 2020 года по 30 августа 2020 года, формула должна считать выручку 15 августа 2019 года по 30 августа 2019 года.
 
 
 
#4
07.10.2020 16:05:01
Может, так?
Код
ВыручкаНИ =
VAR _Min =
    CALCULATE ( MIN ( 'Дата'[Дата] ), ALLSELECTED ( 'Дата' ) )
VAR _Max =
    MAX ( 'Дата'[Дата] )
RETURN
    CALCULATE (
        SUM ( Sale[Выручка] ),
        CALCULATETABLE (
            PARALLELPERIOD ( 'Дата'[Дата], -1, YEAR ),
            DATESBETWEEN ( 'Дата'[Дата], _Min, _Max )
        )
    )
Изменено: Максим Зеленский - 07.10.2020 16:09:59 (исправил)
F1 творит чудеса
 
 
 
#5
07.10.2020 20:24:32
Максим, спасибо за вариант, но он не работает. Нет нарастающего итога и при выставлении фильтра на 1 месяц он в каждой ячейке дня выводит сумму за год. Рядом вывел столбец ВыручкаНИ, который корректно считает текущий нарастающий итог.  
Изменено: Sergey Chernichenko - 07.10.2020 20:33:56
 
 
 
#6
08.10.2020 22:18:23
Доброе время суток.
Чуть по паразитирую на мере Максима.
Код
ВыручкаНИ-1год:= Var selDates = ALLSELECTED('Дата')
Var maxDate = MAX('Дата'[Дата])
Var thisDateRange = CALCULATETABLE(VALUES('Дата'[Дата]); selDates; 'Дата'[Дата] <= maxDate)
Var beforeDates = DATEADD(thisDateRange; -1; YEAR)
Return CALCULATE(SUM('Sale'[Выручка]); beforeDates)
Изменено: Андрей VG - 08.10.2020 22:27:38 (Чуть упростил)
 
 
 
#7
09.10.2020 20:08:40
При замене PARALLELPERIOD на DATEADD всё заработало

Цитата
Андрей VG написал:  по паразитирую на мере Максима.
Андрей, проверил, "паразитирование"))) работает корректно. Спасибо за ваш вариант
 
 
 
#8
10.10.2020 02:04:26
С вашего позволения продолжу тему. Есть потребность в расчете нарастающим итогом плана продаж. Но здесь есть нюанс - план выставляется на квартал. В году 4 квартала. На каждый квартал - свой план. Для расчета используется логика - нарастающий план равен полному плану за квартал, умноженному на количество прошедших рабочих дней и делённому на полное количество рабочих дней в квартале. Под эту логику написана мера:

ПланНИ =CALCULATE(SUM('Дата'[РД]);FILTER(ALLSELECTED ('Дата');'Дата'[Дата]<=max('Дата'[Дата])))/CALCULATE(SUM('Дата'[РД]);DATESBETWEEN('Дата'[Дата];STARTOFQUARTER('Дата'[Дата]);ENDOFQUARTER('Дата'[Дата])))*CALCULATE(SUM('ПланПрод'[ПланВыруч]);DATESBETWEEN('Дата'[Дата];STARTOFQUARTER('Дата'[Дата]);ENDOFQUARTER('Дата'[Дата])))

Мера корректно работает внутри квартала. Но если взять годовой промежуток, образуются скачки на даты при переходе с одного квартала на другой. И конечный итог, вычисляемый мерой, некорректный (больше). Это наглядно видно на графике на примере. Подозреваю, что здесь должен быть какой-то цикл, который проходит по кварталам и потом суммирует итог. Но как его создать, не знаю. Если у вас есть какие-либо мысли по данной задаче, буду благодарен.
Изменено: Sergey Chernichenko - 10.10.2020 02:05:10 (вложил пример)
 
 
 
#9
10.10.2020 11:04:06
Вариант. Переделал структуру отношений календаря с таблицей планов. Плюс, добавил необходимые вычисляемые поля в планы.
 
 
 
#10
10.10.2020 11:24:01
Андрей, спасибо за вариант! Но он имеет недостаток. Нарастающий итог плана распределен по календарным дням. А необходимо распределение нарастающего итога по рабочим.
 
 
 
#11
10.10.2020 11:29:38
Цитата
Sergey Chernichenko написал:
А необходимо распределение нарастающего итога по рабочим.
А где определение того, какой столбец в Календаре отвечает за признак рабочего дня? Вы действительно считаете что я должен догадываться? Плюс, кто вам мешает дополнить вычисление действующих дат календаря фильтрацией на рабочие дни?
Код
Var selDates = CALCULATETABLE('Дата'; FILTER(ALLSELECTED('Дата'); 'Дата'[Дата] <= thisLastDate))
Плюс на забудьте дополнить функцию, учётом рабочих дней, для вычисляемого столбца ПланДень.
Помощь на форуме предполагает в том числе, что вы тоже участник этого процесса, а не просто потребитель в магазине.
Изменено: Андрей VG - 10.10.2020 11:41:28
 
 
 
#12
10.10.2020 12:41:02
Андрей, признаю, не написал. За РД отвечает столбец РД в календаре (в нем установлено значение 1 по рабочим дням в 2020 году. Спасибо за дополнение. Потребителем не буду, переработаю информацию и выложу вариант. Это достаточно массовая задача, которая у многих пользователей требует решения. Единственный шанс невыкладки - если не справлюсь. Но вы мне реально в ней помогли, я должен справиться. Спасибо.
 
 
 
#13
10.10.2020 13:53:53
Изменения в мере
Код
Var selDates = CALCULATETABLE('Дата'; FILTER(ALLSELECTED('Дата'); 'Дата'[Дата] <= thisLastDate && 'Дата'[РД] = 1))

Изменения в вычисляемом столбце ПланДень
Код
='ПланПрод'[ПланВыруч] / COUNTROWS(FILTER(RELATEDTABLE('Дата'); 'Дата'[РД] = 1))
 
 
 
#14
10.10.2020 20:15:25
Во вложении переработанный файл. Точнее, не переработанный, а созданный Андреем. Оставил в нем и первый его вариант по календарным дням (Мера "План НИ 2"), вдруг кому-то необходим вариант с календарными днями. Мера "План НИ 3" работает по варианту рабочих дней. Обратите внимание, что рабочие дни установлены в календаре только на 2020 год, если будете пользоваться позже, не забудьте обновить значения в столбце 'Дата'[РД].

Отдельный и глубокий респект Андрею за простой и изящный алгоритм решения. Мой уровень знаний пока не позволяет так решать подобные задачи. У меня было подозрение, что без использования доп столбца это будет трудно. Но от подозрения до решения - пропасть. Андрей, ещё раз спасибо.
 
 
 
#15
11.10.2020 11:44:46
Выявил, что данная структура работает только в одномерном плане. В случае двухмерного плана "Дата-Сотрудник" не работает. Понимаю почему (План в текущей структуре должен иметь уникальные поля для связи с таблицей дат, но при таком варианте это невозможно). Если справлюсь, выложу вариант решения
 
 
 
#16
11.10.2020 12:31:01
Сергей, у вас пока большие проблемы с логикой построения иерархии в модели.
P. S. И давайте всё же не наглеть. Тема распределение планов нарастающим итогом практически не имеет отношения к сдвигу дат :)  Создайте, пожалуйста, отдельную тему.
Изменено: Андрей VG - 11.10.2020 12:33:31
 
 
 
#17
11.10.2020 13:48:55
Андрей, всегда с уважением отношусь и прислушиваюсь к мнению профи, который знает больше меня. Если вы считаете, что у меня пока большие проблемы с логикой построения иерархии в модели, скорее всего так и есть. Но приведенная вами последняя модель не решает вопрос фильтрации и вывода данных по 2-м параметрам - дате и сотруднику. Она, как и предыдущая, позволяет производить фильтрацию только по дате. У меня смутное ощущение, что всё же таблицы плана и дат при 2-х и более параметрах плана должны быть связаны  по столбцам дат. но это пока только ощущения.

Однозначно подтверждаю, что тема "dax нарастающим итогом со сдвигом дат" решена частично Максимом Зеленским и полностью вами. Я продолжил эту ветку со схожими вопросами только по неопытности общения в форуме. Сейчас точно понимаю, что тем форумчанам, которые пользуются поиском, будет тяжелее найти необходимую инфу по работе с планом продаж в ветке "dax нарастающим итогом со сдвигом дат". Поэтому, по вашей рекомендации, создам отдельную тему.

Если вы считаете, что потратили на эту тему больше времени, чем обычно это происходит в форуме, дайте мне знать. Я не люблю быть должным. Думаю, остальные форумчане не в накладе, они получили необходимые алгоритмы и меры))). Возможно кто-то вспомнит добрым словом вас за эту помощь. Я уже своё мнение высказал.
 
 
 
#18
11.10.2020 14:05:12
Цитата
Sergey Chernichenko написал:
Если вы считаете
Я считаю, что продолжать следует в новой теме. Вашу проблему такая иерархия - решает. Вы просто пока рассматриваете меры - как нечто универсальное. Но универсальных их не много: сумма, счёт, среднее и т. д. Большинство же худо-бедно мер зависят от контекста. Следовательно
Цитата
Sergey Chernichenko написал:
вывода данных по 2-м параметрам - дате и сотруднику
нужно представлять в примере этот контекст. В рамках же приложенного примера - имеется рабочий результат. Ещё раз - давайте продолжать в новой теме :)
 
 
 
Страницы: 1
Наверх