Страницы: 1 2 След.
RSS
Расчет LFL выручки в DAX
 
Добрый день.
Очень нужно посчитать прирост выручки по региону по отделам которые работали в оба сравниваемых периода (к примеру январь 2017 и январь 2018).
Есть выгрузка (во вложении). Все до чего додумался за две недели это сгруппировать по дням и применить такую формулу:
Код
LFL предыдущий год мера = CALCULATE(
                                sum('Таблица1'[Сумма группы]) ; 
                                                                 DATEADD('Таблица1'[ДатаБезВремени].[Date]; -1 ; YEAR))

но не нашел способа как убрать выручку отделов которые не работали год назад, да и не уверен, что эта формула корректно считает.
 
Вроде ваша должна работать.
Не очень понятно, какой именно результат нужно получить, но примерно так можете попробовать:

Добавьте вычисляемый столбец [Year]=YEAR([Дата])
Код
Сумма LY = CALCULATE([Сумма];FILTER(ALL('Таблица1'[Year]);'Таблица1'[Year]=MAX('Таблица1'[Year])-1))
Код
Сумма CY (LFL) = CALCULATE([Сумма];FILTER(VALUES('Таблица1'[Отдел]);[Сумма LY]<>0))
Код
LFL %% =DIVIDE([Сумма CY (LFL)];[Сумма LY])

Скрытый текст
F1 творит чудеса
 
Доброе время суток
Что-то, Максим, не убедительно. Простейший пример. По условию
Цитата
Den-is написал:
по отделам которые работали в оба сравниваемых периода (к примеру январь 2017 и январь 2018).
То есть Сумма LY = 90, а Сумма CY = 100.
Updated.
Как-то так.
Изменено: Андрей VG - 29.08.2018 19:33:45
 
Доброго утра. Спасибо за помощь.
Максим, спасибо, да мой вариант в принципе работает, проблема в том что это только первая часть вычислений и да делал в BI (спасибо за проницательность и подсказку на перед), а до второй не мог додуматься. Буду пробовать.

Андрей, Вам большущееее спасибо, по сути по Вашим комментария на этом сайте и пытаюсь освоить DAX. Ваш вариант пока не удалось посмотреть (на рабочем компе Pivot не поставить). На выходных попробую разобраться. Обязательно напишу, что лучше подойдет.
 
Забавное различие в работе мер между Power Pivot 2010 и Power BI Desktop
В Excel
Код
Сумма CY2:=CALCULATE([Sum of];
    CALCULATETABLE(VALUES('Таблица1'[Отдел]); FILTER(ALL('Таблица1'[Год]); 'Таблица1'[Год] = MAX('Таблица1'[Год]) - 1));
    VALUES('Таблица1'[Отдел])
)
Sum of:=SUM('Таблица1'[Сумма])

То же в Power BI Desktop
Код
Сумма CY (Excel) = CALCULATE([Сумма];
    CALCULATETABLE(VALUES('dataTable'[Отдел]); FILTER(ALL('dataTable'[Год]); 'dataTable'[Год] = MAX('dataTable'[Год]) - 1));
    VALUES('dataTable'[Отдел])
)
Сумма = SUM('dataTable'[Остаток])

Но в Power BI Desktop мера возвращает BLANK() и работает только в таком виде
Код
Сумма CY = CALCULATE([Сумма];
    CALCULATETABLE(VALUES('dataTable'[Отдел]); FILTER(ALL('dataTable'[Год]); 'dataTable'[Год] = MAX('dataTable'[Год]) - 1));
    VALUES('dataTable'[Отдел]);
    FILTER(ALL('dataTable'[Год]); 'dataTable'[Год] = MAX('dataTable'[Год]))
)
С чего бы понадобился сброс фильтра?
 
Андрей, спасибо то, что нужно. Если вопрос ко мне, то скорее всего чтобы по строке январь 2018 показывались данные за 2017 год.
 
Цитата
Den-is написал:
Если вопрос ко мне
Не, вопрос не к вам, а в общем. Почему мера для текущего года так странно себя ведёт. Ведь, условно говоря, в источнике строк Год 2018, следовательно там уже только отделы, у которых есть названия в этом году. VALUES('dataTable'[Отдел])
А отделы, названия которых есть в 2017 определяются CALCULATETABLE(VALUES('dataTable'[Отдел]); FILTER(ALL('dataTable'[Год]); 'dataTable'[Год] = MAX('dataTable'[Год]) - 1))
Пересечение же этих табличных фильтров и даёт таблицу имён отделов, которые есть и там и там. По примеру - это отдел А. Для чего фильтр по году-то ещё нужно устанавливать? Ведь в Power Pivot 2010 это работает без этого шаманства.
 
Вообще очень странное поведение. Вот такая мера дает правильный результат (1 для 2018 года) в Power BI:
Код
Количество отделов LFL =
CALCULATE (
    COUNTROWS ( VALUES ( 'dataTable'[Отдел] ) );
    CALCULATETABLE (
        VALUES ( 'dataTable'[Отдел] );
        FILTER (
            ALL ( 'dataTable'[Год] );
            'dataTable'[Год]
                = MAX ( 'dataTable'[Год] ) - 1
        )
    );
    VALUES ( 'dataTable'[Отдел] )
)

Но стоит заменить COUNTROWS ( VALUES ( 'dataTable'[Отдел] ) ) на [Сумма], и ничего не считает... Бред какой-то
Думал, что проблема во втором CALCULATE (не, ну бред, но всё же...), поменял на SUM('dataTable'[Остаток]), и опять пусто (естественно, что разницы с [Сумма] не должно быть.
Продолжаю ломать голову.
F1 творит чудеса
 
Андрей VG, вообще конечно надо было всё сразу делать по паттерну, я его тоже начал делать в начале, но затем мне показалось, что и так норм - наверное, запутали исходные данные. Вот результаты моих первых формул:

Но да, на других вводных неправильный результат дает, естественно
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Но стоит заменить COUNTROWS ( VALUES ( 'dataTable'[Отдел] ) ) на [Сумма], и ничего не считает...
Может это последствия нововведений последней версии? Как у меня с развёртыванием сжатого в base64 Json. Так из предложений сработало только отключение композитных моделей. Приём с созданием запроса сначала к файлу csv, а потом заменой кода на мой - не сработал, тоже сообщение об ошибке.
 
Напишу в MVP-ный чатик, глядишь, так Марко быстрее ответит. По всем признакам - бага, не может такого быть. Хотя может быть, что тут как-то задействован AUTOEXIST, но нет, не та история...
F1 творит чудеса
 
Так и есть - Марко говорит, что это бага, так не должно быть. Начали разбираться.

В зависимости от того, как построена модель, есть несколько обходов.
Проще всего - когда есть нормальная модель звездочкой с полным календарем (либо даже модель со встроенной иерархией дат - так как там полный календарь автоматически формируется).
Несколько обходных маневров - в файле. Особенно порадовала история с SUMX - интересные результаты в обоих случаях - со звездой и без звезды :)
F1 творит чудеса
 
Максим, большое спасибо за оперативное реагирование и обходы до исправления бага. Странно, это только в Power BI Desktop или в 2013-2016 тоже (в 2010 такого нет)?
 
В 2013 такого нет, работает как ожидается. Может быть и в 2016/365 нет - пока не проверял.
F1 творит чудеса
 
Доброго времени суток!
Пытаюсь разобраться в решении, но для начала не могу понять, исправилась бага или нет?
Я понял что нет, поскольку коды №2 и  №3 из сообщения  #5 по прежнему дают разные результаты в PBI, а точнее№2 не тот результат(100 в строке и 260 в итоге), а №3 нужный(100 и в строке и в итоге).
Но и код №1 в excel 2016 и в Office 365 выдает пустой результат.
Изменено: Lari - 18.07.2020 15:24:08
 
Прошу помочь с решением этой же задачи, не могу осилить,
как рассчитать суммы по соответствующим отделам и соответствующим дням в текущем и прошлом годах.
т.е. учитывать те значения ИТОГО, где есть значения в соответствующих днях и в соответствующих отделах.
Пример все тот же , но звезда.
 
Доброго дня. Решение кривое и гуру точно не одобрят, по этому не стал выкладывать))
Но мои отчёты два года на нем работают все точно считается. В общем есть таблица по которой нужно посчитать LFL копируем её создаём столбец в котором смещаем дату на год ( DATEADD) Далее через впр (LOOKUPVALUE) подтягиваем данные в исходную таблицу. У нас получается таблица в которой в одной строке указан период за который расчёт делаем отдел сумма этого периода и сумма периода год назад. Далее уже мерой можно посчитать по аналогии с суммеслимн. Только вот LFL считать по дням сомнительная затея, по мне единственный правельныы вывод который можно сделать на основании дня это то что в разные дни недели разные продажи, тут минимум на месяц. Нужно переходить.
 
Вариант
 
anvg, спасибо за ответ, уточните, пожалуйста, из формулы я не смог понять , если я из фильтра уберу отдел 17, то формула будет считать соответствующие дни у разных отделов или у одного отдела?
Нужно чтобы значения столбца "Итого" считались по соответствующим датам в соответствующих отделах даже без фильтра на отдел.
Изменено: Lari - 19.07.2020 14:25:43
 
Цитата
Lari написал:
из формулы я не смог понять
Поскольку нет вводных определений как входа, так и выхода, что есть что, а я не специалист в области вашей отрасли от слова совсем, нет определения результата, то предложенная мера всего лишь предложение для размышления для вас, как участника решения ВАШЕЙ же задачи. Это не раздел Работа.
Не существует универсальных мер. Уже среднее - может оказаться средним по больнице и бессмысленным по существу. Поэтому без определения измерений, без определения что есть что и как это что и что между собой взаимодействует, не возможно получить правильного решения.
Простейшее для перехода к отделам, нужна, как минимум, дополнительная группировка по отделам в SUMMARIZE. Опять же - с моей точки зрения - это гипотеза, так как смотрите выше... И где в примере результата в вашем файле пример, что должно получиться для нескольких отделов? Частный пример результата приводит к частному решению :)
Изменено: anvg - 19.07.2020 14:38:27
 
anvg,прошу пощения за неточное формулирование вопроса и спасибо за уже оказанную помощь.

Скорректировал пример, с более подробным примером поведения расчетов.
Интересует прирост по соответствующим отделам и датам.
 
Вариант №2 :)
 
anvg, огромное спасибо!
Посмотрел код- как заглянул в летающую тарелку.
Моим бумажным самолетикам такое и не снилось)).
 
Вот так короче и понятнее, наверно:
Код
Difference :=
VAR _t =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( 'Отдел'[Отдел] ); VALUES ( 'Календарь'[Date] ) );
            "SumNow"; CALCULATE ( SUM ( 'Факты'[Итог] ) );
            "SumPrev"; CALCULATE ( SUM ( 'Факты'[Итог] ); DATEADD ( 'Календарь'[Date]; -1; YEAR ) )
        );
        NOT ( ISBLANK ( [SumNow] ) ) && NOT ( ISBLANK ( [SumPrev] ) )
    )
RETURN
    DIVIDE ( SUMX ( _t; [SumNow] - [SumPrev] ); SUMX ( _t; [SumPrev] ) )
 
StepanWolkoff, большое спасибо, тоже добавил в свой сборник формул)
 
Доброго времени суток!
DATEADD(Date(2020;02;29);-1; YEAR) будет возвращать значения 28 февраля 2019-го.
Можно ли этого избежать?
Код
IF (MONTH ('Календарь'[Date]) = 2 && DAY ('Календарь'[Date])=29 ;BlANK() ; DATEADD( 'Календарь'[Date]; -1; YEAR ) )
Такая формула не работает

Кстати формула #24 на больших данных быстрее всю оперативку отъедает и может вылетать.
Изменено: Lari - 04.03.2021 17:12:37
 
Цитата
Lari написал:
будет возвращать значения 28 февраля 2019-го.
А вам надо чтобы что возвращала?
Цитата
Lari написал:
Кстати формула #24 на больших данных быстрее всю оперативку отъедает и может вылетать.
Ну дык делать виртуальные таблицы с такой гранулярностью (по дням) оно и понятно. Надо по месяцам хотя бы. А так, да, жесть.
Изменено: PooHkrd - 04.03.2021 17:32:33
Вот горшок пустой, он предмет простой...
 
Calculate
в нем:
1. Сумма текущего периода;
2. DATEADD()
3. Столбец с датами <> “28.02.2019”

но как бы зачем убирать этот день в 2019 году?

а вот 29.02.2020 убрать стоило бы. Некорректные представления продаж из-за этого високосного года
 
Доброго времени суток!
Цитата
PooHkrd написал:
А вам надо чтобы что возвращала?
Суть формул #22 и #24 в том что они считают только те данные, которые есть в обоих периодах.
Цитата
PooHkrd написал:
А так, да, жесть.
На нескольких млн. строк все нормально работает.
Я отметил что на большем количестве уже съедает память.
Вся соль в гранулярности по дням.
 
ArgentumTiger_7,
такая формула не работает
Код
=
CALCULATE (
    SUM ( 'Sales'[NetPrice] );
    DATEADD ( 'Календарь'[Date]; -1; YEAR );
    'Календарь'[Date] <> DATE ( 2020; 02; 29 )
)
Изменено: Lari - 05.03.2021 06:23:33
Страницы: 1 2 След.
Наверх