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

Варианты форматов данных

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

date	views_f1	views_f2
29.05.21	2951	49
28.05.21	2902	58
27.05.21	2844	62
26.05.21	2782	69
25.05.21	2713	66

Таблица. Пример двух форматов исходных данных

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

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

Проблема

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

Решение

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

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

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

Данное утверждение не всегда верно. Нельзя забывать, что некоторые метрики типа охвата не является просто суммой охватов за каждый день. А также это утверждение не применимо к различным расчетным метрикам типа "показатель отказа", "средняя длительность сеанса" и так далее.

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

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

try [views]-Source{[Index]+1}[views] 
 otherwise [views]

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

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

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

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

Пошаговая инструкция:
Шаг 1. Создаем дубликат таблицы (именно дубликат, а не ссылку, поскольку в дальнейшем мы будем присоединять это таблицу к основной и при использовании ссылки у нас получится циклическая зависимость)
Шаг 2. Делаем смещение даты
1) Преобразуем дату в числовой формат
2) Добавляем единицу
3) Преобразуем число обратно в дату
Шаг 3. Присоединяем таблицу с предыдущими значениями к основной по полю даты.
Шаг 4. Создаем кастомный столбец, где вычитаем из текущего количество показов, данные за предыдущий день
Шаг 5. Профит!

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

Вышеописанный метод будет работать в идеальных условиях, когда в наших данных нет разрывов, а так бывает далеко не всегда, если у вас не хватает данных за какой-то день, то их можно восстановить. Для этого есть разные методы, в данном случае мы просто будем исходить из того, что если данных нет, то прирост в этот день был равен 0. А следовательно, будем брать ближайшее предыдущее непустое значение.

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

Простейший обновляемый календарь в Power Query (создаете пустой запрос и копируете код ниже)
Table.FromList(List.Dates(#date(2016, 04, 1), Duration.Days(Duration.From(Date.From(DateTime.FixedLocalNow()) - #date(2016, 04, 1)))+1,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"})

Пошаговая инструкция:
Шаг 1. Сортируем по убыванию дат нашу таблицу (календарь также должен быть отсортирован по убыванию)
Шаг 2. Присоединяем календарь к основной таблице по дате (нужно использовать правое соединение, чтобы были добавлены все даты из календаря и появились пробелы в показах)
Шаг 3. Удаляем старый столбец с датой (теперь даты из календаря будут нашими датами)
Шаг 4. Используем заполнение вниз на столбце с показами
Шаг 5. Удаляем пустые значения показов (если ваш календарь содержит больше дат чем нужно, то у вас такие записи точно появятся)
Шаг 6. Повторяем действия из метода 1.2

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

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

К сожалению, в некоторых случаях метод 1.3 также не является панацеей. К примеру, если вам не нужна куча лишних записей, которые не несут в себе ценности. Или у вас огромный объем данных и добавление большого количества пустых строк может сильно усложнить работу системы.
В этом случае, использование календаря нежелательно и нам нужно найти предыдущее значение, зная что статистики по некоторым дням может не хватать.
На самом деле задача решается аналогично методу 1.2, но только соединять таблицу с предыдущими значениями мы будем не по дате, а по ключу.

Пошаговая инструкция:
Шаг 1. Сортируем по возрастанию даты
Шаг 2. Создаем дубликат основной таблицы
Шаг 3. Добавляем столбец-индекс для основной таблицы (начинаем с 0)
Шаг 4. Добавляем столбец-индекс для таблицы дубликата (начинаем с 1)
Шаг 5. Присоединяем к основной таблице таблицу-дубликат по столбцам-индексам (используем левое соединение)
Шаг 6. Заменяем null на 0 в значениях показов из таблицы-дубликата
Шаг 7. Создаем кастомный столбец, где вычитаем из текущего количество показов, данные за предыдущий день (из таблицы-дубликата)
Шаг 8. Профит!

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

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

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

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

_views_d = SUM(data13[views]) - CALCULATE(SUM(data13[views]), PREVIOUSDAY(data13[Date]))

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

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

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

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

_views_total = 
IF(ISBLANK(FIRSTNONBLANK(data3[statDate],1)), BLANK(),
    CALCULATE(
        SUM(data3[views]),
        DATESBETWEEN(
            data3[statDate],
            BLANK(),  
            MAX(data3[statDate])
        )
    )
)

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

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

List.Sum(List.Range(#"AddedIndex"[views],0,[Index]))

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

Напоминание: если что-то непонятно, то скачайте с гитхаба файл .pbix, там реализованы все методы из данной статьи. Ссылка в конце статьи.

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

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

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

Mode	Index
Total	1
Delta	2

Таблица. ListMode

Обратите внимание: Power BI так и норовит связать эту вспомогательную табличку с чем-либо, но никакие связи здесь не нужны.

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

_MultiMeasure = SWITCH(SUM(ListMode[Index]),
    1, SUM(data1[views]),
    2, SUM(data1[views_d])
)

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

Ссылка на гитхаб

https://github.com/q-stat-ru/art06_pbi_total_vs_by-day

На гитхабе можно найти примеры использования описанных выше методов.
Данные на гитхабе относятся к проекту — https://q-stat.ru/google-street-view-panoramas-statistics /