Страницы: 1
RSS
Суммирование в скользящем окне в Power Pivot
 
Коллеги, привет, нужна помощь.
необходимо по каждой позиции товарной матрицы считать сумму/среднее за последние N дней.
По идее должно быть что-то вроде такого:
Код
=CALCULATE(SUM([Продажи]);
FILTER('Таблица1';
'Таблица1'[Точка]=EARLIER([Точка]) && 
'Таблица1'[Товар]=EARLIER([Товар]) && 
'Таблица1'[Дата]<=EARLIER([Дата]) &&
'Таблица1'[Дата]>=DATEADD(EARLIER('Таблица1'[Дата]);-2;DAY)
))

Но в последней строке dateadd должен ссылаться на столбец, и в него на завернуть earlier.

Файл с примером прилагаю, добавил формулами обычного эксель столбец "должно быть так", который считает нужную сумму.

Изменено: PavelZakharov - 02.07.2020 01:19:27
 
Доброе время суток.
Ну, в данном случае с DATEADD можно не заморачиваться, хотя вопрос конечно интересный
Скрытый текст
 
Блин, чойта слепочусь слегка. Показалось что вопрос про PQ. Все равно оставлю ответ под спойлером на всяк случай.
Скрытый текст
Ну и мой вариант форумулы:
Код
=
CALCULATE (
    SUM ( 'Таблица1'[Продажи] );
    FILTER (
        ALLEXCEPT (
            'Таблица1';
            'Таблица1'[Точка];
            'Таблица1'[Товар]
        );
        'Таблица1'[Дата]
            <= EARLIER ( 'Таблица1'[Дата] )
            && 'Таблица1'[Дата]
                >= (
                    EARLIER ( 'Таблица1'[Дата] ) - 2
                )
    )
)

Или даже так, безо всяких EARLIER
Код
=
VAR d = 'Таблица1'[Дата]
RETURN
    CALCULATE (
        SUM ( 'Таблица1'[Продажи] );
        FILTER (
            ALLEXCEPT (
                'Таблица1';
                'Таблица1'[Точка];
                'Таблица1'[Товар]
            );
            'Таблица1'[Дата] <= d
                && 'Таблица1'[Дата] >= ( d - 2 )
        )
    )
Изменено: PooHkrd - 02.07.2020 09:27:04
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
, type table[Точка = text, Товар = text, Дата = date,
Скажите, только у меня вот это никогда не работает? Например, даты при выгрузке на лист или в модель данных выгружаются типом datetime
 
Цитата
Михаил Л написал:
только у меня вот это никогда не работает?
Из текста не очень понятно, что именно не работает?
Прописывание типов полей в группировке? Такое возможно, ибо на сколько я помню у вас 2013 эксель и надстройка, хотя странно, вроде бы типизация полей таблицы это базовый элемент языка, в него вроде изменения особо не вносят. А даты по умолчанию и у меня показываются как datetime. Достаточно посмотреть на отображение этого столбца в шаге Source. Сама по себе такая запись с типами полей при группировке прописывается в PQ автоматически в O365 где-то с год уже. И даже там по умолчанию для полей с содержимым похожим на даты автоматом прописывается тип datetime, это я уже руками правил.
У вас, кстати, и для DAX моя нижняя формула не сработает, только формула Андрея или моя верхняя.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
такая запись с типами полей при группировке прописывается в PQ автоматически в O365 где-то с год уже
Вот и ответ :)  Теперь все стало понятно. Я то думал что руками прописывается

Я обычно наоборот, когда запрос готов, стираю все эти штуки(, type table  , type record  , type list)
 
Цитата
Михаил Л написал:
когда запрос готов, стираю все эти штуки(, type table  , type record  , type list)
ну и зря. Прописывание типов при группировке помогает разворачивать сгруппированные таблицы сразу с правильными типами данных. Иначе информация о типах столбцов теряется, они становятся any и их надо опять задавать
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
помогает разворачивать сгруппированные таблицы сразу с правильными типами данных
Потому и написал
Цитата
Михаил Л написал:
когда запрос готов,
Так то да, без прописывания типа не будет двух-главой стрелочки
 
Во-первых, стрелочка будет, никуда она не денется.
Я имел ввиду немного другое:
Код
// FullType
let
    Source = #table(type table [Дата = date, Целое = Int64.Type, Десятичное = number], {{#date(2020,1,1),42,79.9999},{null,null,null}}),
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Индекс"}, {{"Rows", each _, type table [Дата=date, Целое=Int64.Type, Десятичное=number, Индекс=number]}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Дата", "Целое", "Десятичное"}, {"Дата", "Целое", "Десятичное"})
in
    #"Expanded {0}"

Код
// NoType
let
    Source = #table(type table [Дата = date, Целое = Int64.Type, Десятичное = number], {{#date(2020,1,1),42,79.9999},{null,null,null}}),
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Индекс"}, {{"Rows", each _}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Дата", "Целое", "Десятичное"}, {"Дата", "Целое", "Десятичное"})
in
    #"Expanded {0}"

В первом случае вам не нужно потом типизировать столбцы заново перед загрузкой в модель:

И, кстати, любопытные результаты получаются, если поиграться с описанием типа при группировке.
Заодно, если группируем не в таблицу, а, например, один столбец, тоже можно сохранять тип данных при группировке для разворота, например, так:
Код
type {number}
F1 творит чудеса
 
Максим Зеленский, я всего то хочу сказать что такое прописывание типа не дает такой же эффект как у функции Table.TransformColumnTypes
Ну что это такое:
Код
// FullType
let
    Source = #table(type table [Дата = date, Целое = logical, Десятичное = number], {{#datetime(2020,1,1,0,0,0),0,79.9999},{null,1,null}}),
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Индекс"}, {{"Rows", each _, type table [Дата=date, Целое=logical, Десятичное=number, Индекс=number]}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Дата", "Целое", "Десятичное"}, {"Дата", "Целое", "Десятичное"})
in
    #"Expanded {0}"
 
Андрей VG, PooHkrd, большое спасибо за ответы, всё работает.

PooHkrd, сохраню ваше решение для pq, оно тоже может быть полезным, спасибо.
 
Цитата
Михаил Л написал:
не дает такой же эффект как у функции Table.TransformColumnTypes
а почему оно должно делать то же самое?..
Есть тип значения, есть тип столбца, есть тип таблицы. Table.TransformColumnTypes делает приведение значений к указанному типу столбца. А то, что я привел в примере, делает указание типа столбца без приведения значений к типу и проверки данных на соответствие. Равно как и указание типа при добавлении столбца.
Я даже больше могу показать:
Код
Source=#table(type table [Date = date], {{"asdf"}})


Код
= Table.AddColumn(Source, "Новый", each Value.Is([Date], type date), type number)

F1 творит чудеса
 
Цитата
Максим Зеленский написал:
а почему оно должно делать то же самое?..
Есть тип значения, есть тип столбца, есть тип таблицы. Table.TransformColumnTypes делает приведение значений к указанному типу столбца
Вот с типом столбца и типом таблицы частенько сталкиваюсь
А вот тип значения пока не вижу для чего можно применить, поэтому и начал этот разговор.
Теперь мне стало ясно различие всех этих типов
 
Коллеги, добрый день :excl:

Обращаюсь к вам за помощью :cry:
Только познаю азы PP и PQ. :(

Вопрос чем-то схож с текущей темой.

Стоит задача найти продажи по максимально поздней дате продаж в 1 точке и по 1 продукту. Поиск ограничить последними 12 месяцами (MAT - 12 месяцев назад от текущего месяца), т.е. период смещенный.
В экселе расчет могу сделать, а как его перевести в меру PP.

Что необходимо от меры:
1. Найти продажу максимальной даты в рамках скользящих 12 месяцев по конкретной точке в рамках продукта
2. Найдя эту продажу автоматически скопировать на все 12 месяцев этой точки, т.е. нашла, что максимальная дата среди:
Дата       Продажи
июл.22 -  3уп
сент.22 -  2уп
янв.23 -  4уп
Определила янв.23 как максимальную дату и результат этой меры дублируется для каждой строчки с этим адресом и продуктом:
Дата       Продажи  Мера
июл.22 -  3уп           4уп
сент.22 -  2уп           4уп
янв.23 -  4уп            4уп

Если правильно понимаю, то часть кода по выборке могу использовать из предложенных выше вариантов, однако как прописать код по анализу скользящего периода и лоцирования на предыдущие даты?

Код
=
CALCULATE (
    SUM ( 'Таблица1'[Продажи] );
    FILTER (
        ALLEXCEPT (
            'Таблица1';
            'Таблица1'[Точка];
            'Таблица1'[Товар]
        );
        '[B]Таблица1'[Дата]
            <= EARLIER ( 'Таблица1'[Дата] )
            && 'Таблица1'[Дата]
                >= (
                    EARLIER ( 'Таблица1'[Дата] ) - 2
                )
    )[/B]
) 


Заранее благодарна за помощь в познании PQ.
Страницы: 1
Наверх