Накопительный итог VS Данные по дням

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

1. Суммарно на дату — у ролика на ютуб на 29 мая 2024 года было 2951 просмотров
2. По дням — у ролика на ютуб за 29 мая 2024 года было 49 просмотров, за 28 мая 2024 года было 58 просмотров и т.д.

Проблема

Очень редко, когда данные можно получить сразу в двух форматах, при этом смотреть данные в обоих вариантах иногда бывает очень полезно.

Решение

Ниже мы рассмотрим варианты преобразования формата данных от одного к другому и какие могут возникать проблемы при этом.

Часть 1. Разбиваем накопительный итог по дням

Мы будем исходить из следующего утверждения — если на сегодня у ролика 90 показов, а вчера было 59, то за сегодня прибавился 31 показ.

Метод 1.1. Power Query. Кастомный столбец

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

В данном случае:
Source («Источник» в русской версии) — техническое слово, указывающее на вашу текущую таблицу
[Index] — столбец с индексом
[views] — столбец с показами
Всё это оформлено в структуру try otherwise, чтобы для строчки в которой нет предыдущего значения показов (самой последней) вместо ошибки получить количество показов на текущую дату.

Что делать, если строчки относятся не к одному ролику, а к несколькими, а также просто другие подходы к решению этой задачи мы рассмотрим ниже.

Метод 1.2. Power Query. Таблица-дубликат с предыдущими значениями

Вторым вариантом, чтобы узнать сколько было показов в предыдущий день будет взять нашу таблицу, сделать дубликат и сделать смещение. После чего можно объединить ее с основной таблицей.

Метод 1.3. Power Query. Используем календарь (заполнение пробелов)

Для этого нам понадобится календарь, который будет покрывать весь интересующий интервал, при этом в нём не должно быть разрывов. Способов создания календаря очень много, в качестве примера можно использовать тот, что написан ниже.

Метод 1.4. Power Query. Используем ключ

Что делать, если роликов несколько?

Если в ваших данных есть информация не по одному, а по нескольким роликам, вам необходимо:
1) присоединять данные за предыдущий период, не только по ключу (или дате), а также и по идентификатору ролика;
2) при осуществлении сортировки, необходимо в начале производить сортировку по идентификатору ролика, а потом уже по дате.

Метод 1.5. DAX. Считаем простую меру

Помимо разнообразных способов посчитать изменение данных за сутки с помощью Power Query, естественно, также есть способ посчитать тоже самое с помощью DAX. Мы рассмотрим самый простой способ — создание меры для данных без пропусков и когда все данные относятся к одному ролику.

Часть 2. Собираем накопительный итог из статистики по дням

В данном случае, наоборот, нам предстоит просуммировать статистику показов по всем предыдущим дням, чтобы получить количество показов на конкретную дату.

Метод 2.1. DAX. Считаем меру

Наиболее простым способом в данном случае будет использовать меру:

Метод 2.2. Power Query. Кастомный столбец

Если отсортировать данные по возрастанию даты и добавить столбец с индексом, то можно создать пользовательский столбец таким образом:

Обратите внимание, столбец с индексом при такой формуле должен начинаться с 1. А также, необходимо использовать #»AddedIndex»[views], а не просто [views]. Поскольку в первом случае это список, а во втором значение из столбца [views]. #»AddedIndex» — в данном случае это название последнего шага до создания пользовательского столбца.

Часть 3. Делаем переключение между двумя форматами

После того, как мы получили данные в двух форматах, логично сделать возможность выбора формата выбора.

Для этого нам потребуется вспомогательная табличка ListMode:

Дальше нам нужно создать следующую мультимеру (про них подробнее можно почитать здесь):

И создать соответствующий фильтр на основе поле ListMode[Mode] с помощью которого мы будем переключаться между двумя режимами. Сразу рекомендуется разрешить выбор только одного значения в настройках фильтра.
Далее остается только вывести данную меру на визуализацию.

[expert_bq id=»1570″]Мы же выбираем Table 0, которая на просмотрщике отображается как нужная табличка с курсами валют, и нажимаем Преобразовать данные. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если вы захотите применить к этой табличке банальное вычитание или сложение, то разживётесь ошибкой «ЗНАЧ». Она вылезает тогда, когда имеются неточности с форматами. Поэтому давайте сразу в запросе зададим нужный формат ячеек, а именно – валюту.

Функция ВПР в Excel для чайников и не только

1. Суммарно на дату — у ролика на ютуб на 29 мая 2024 года было 2951 просмотров
2. По дням — у ролика на ютуб за 29 мая 2024 года было 49 просмотров, за 28 мая 2024 года было 58 просмотров и т.д.

Подключите DAX Studio к файлу Excel
Нажмите «Подключиться». После того, как соединение установлено, не закрывайте DAX Studio, программа должна быть открыта.
Теперь все готово для подключения.
[expert_bq id=»1570″]Вот и всё, теперь у меня есть интерактивная диаграмма и достаточно щёлкнуть кандидата, чтобы подсветилась его часть набранных голосов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Динамические запросы в Power Query можно делать с помощью встроенного языка Mmsdn.microsoft.com/en-us/library/mt253322.aspx, однако запросы крайне неустойчивы в плане изменения каких-либо параметров в них. Чтобы запрос оставался «постоянным» сделаем следующий прием:

Синтаксис функции List.Contains

Обязательно сделайте ваши таблицы умными — щёлкнуть в любую ячейку и нажать Ctrl+T, в появившемся окне «Ок», не снимать галку «Таблица с заголовками». Если будете использовать Power Query — у вас уже будут умные таблицы.