Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)
TOTALYTD, TOTALQTD и TOTALMTD — DAX функции категории time intelligence, вычисляющие выражение в текущем периоде в Power BI и Power Pivot
Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы разберем несколько функций языка DAX, из категории time intelligence (логики операций с датами и временем), которые вычисляют заданное выражение в текущем периоде в Power BI и Power Pivot. А именно, изучим функции TOTALYTD, TOTALQTD и TOTALMTD, вычисляющие выражение, в соответствии со своим названием, от начала года, квартала или месяца до текущей даты.
Напомню, что в предыдущей статье мы рассматривали похожие функции, но, которые, не вычисляют выражение как TOTALYTD, TOTALQTD и TOTALMTD, а возвращают набор дат, также, для текущего периода.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.
Да, и еще один момент, до 24 декабря 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 75% (вместо 20000, всего за 5000 руб.)
В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.
Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 75% (до 24 декабря 2021 г.): узнать подробнее
до конца распродажи осталось:
DAX функции TOTALYTD, TOTALQTD, TOTALMTD в Power BI и Power Pivot
TOTALYTD () — вычисляет заданное выражение от начала года до текущего дня в рамках текущего контекста.
TOTALQTD () — вычисляет заданное выражение от начала квартала до текущего дня в рамках текущего контекста.
TOTALMTD () — вычисляет заданное выражение от начала месяца до текущего дня в рамках текущего контекста.
! — Для корректной работы функций TOTALYTD, TOTALQTD и TOTALMTD необходимо в качестве их параметров [Дата] использовать столбец из календаря непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.
Пример формул на основе time intelligence функций группы TOTAL (YTD, QTD и MTD)
В модели данных Power BI Desktop имеется исходная таблица «Продажи-Затраты», содержащая даты и по каждой дате, соответственно, сумму продаж и сумму затрат за 2 года (с 1.01.2017 по 31.12.2018):
Задача состоит в следующем: создать отчет в Power BI, который бы показывал накопленную прибыль компании за весь текущий период. А если конкретнее, то в отчете должны быть три визуализации вида «Матрица» и в каждой визуализации отображена накопленная прибыль за 3 периода (в первой таблице — итог по прибыли за текущий год, во второй — итог по прибыли за текущий квартал и в третьей — накопленный итог прибыли за текущий месяц).
Так как накопленную сумму прибыли в этом примере нужно рассчитывать от начала текущего года / квартала / месяца до текущей даты, то это все можно легко решить при помощи, рассматриваемых в этой статье, time intelligence функций языка DAX: TOTALYTD, TOTALQTD и TOTALMTD. Именно они в Power BI рассчитывают любые выражения в рамках конкретного периода текущего контекста.
В исходной таблице суммы прибыли организации у нас нет, поэтому, нам ее нужно рассчитать. Для этого, в качестве параметра «Выражение» в функциях TOTALYTD, TOTALQTD и TOTALMTD нужно прописать формулы расчета самой прибыли, которая равняется разнице сумм продаж и затрат. И рассчитаем мы ее при помощи еще одной функции в DAX — SUMX, о которой Вы можете подробно прочитать в этой статье.
И перед тем, как написать итоговые формулы, нам осталось обсудить еще один момент. Дело в том, что функции TOTALYTD, TOTALQTD и TOTALMTD не работают в Power BI с датами в исходных таблицах.
В синтаксисе этих функций я писал, что для их использования нужна специальная отдельная таблица «Календарь» с неразрывными датами, связанная с нужной исходной таблицей. Как создавать календари неразрывных дат в Power BI Вы можете прочитать в этой статье.
Поэтому, в нашей модели данных я создал таблицу «Справочник Календарь»:
И связал ее с таблицей «Продажи-Затраты» по столбцу [Дата]:
Итак, теперь мы, наконец, можем написать сами формулы расчета накопленной прибыли за текущий период на основе функций TOTALYTD, TOTALQTD и TOTALMTD:
Для начала, мы создали формулу меры [Прибыль] для того, чтобы, с одной стороны, просто разместить текущий размер прибыли в визуализации Power BI и, с другой стороны, чтобы в 3-х формулах накопленной прибыли не прописывать одни и те же строки несколько раз, тем самым сэкономив и время и место в редакторе формул.
Далее, собственно, расписали, достаточно простые формулы накопленной текущей прибыли по году, кварталу и месяцу. В первом параметре всех 3-х функций мы указали то выражение, которое нужно вычислить. В нашем примере это мера [Прибыль]. Фильтры в формулах мы не использовали просто потому, что в них нет надобности в этом практическом примере. В качестве последнего параметра указали набор непрерывных дат из связанной таблицы «Календарь», по которому эти функции и будут вычислять меру [Прибыль].
Итак, результатом выполнения формулы на основе DAX функции TOTALYTD, в Power BI будет следующая визуализация накопленной годовой прибыли:
В данной визуализации мы видим, что, действительно, сумма прибыли накапливается общим итогом в течение года, и затем, как начинается новый год, итоговая годовая сумма прибыли обнуляется и накапливается вновь.
Результатом выполнения формулы на основе DAX функции TOTALQTD, в Power BI будет следующая визуализация накопленной поквартальной прибыли:
В этой визуализации все также, как и в примере выше, то есть, в первом квартале прибыль накапливается общим итогом каждый месяц, далее, во втором квартале она обнуляется и вновь начинает накапливаться новая поквартальная прибыль.
Результатом выполнения формулы на основе DAX функции TOTALMTD, в Power BI будет следующая визуализация накопленной помесячной прибыли:
И в этой визуализации, опять все также, как и в примерах выше — прибыль накапливается каждый день общим итогом в течение всего месяца. Затем, как начинается новый месяц, общий итог обнуляется и прибыль начинает накапливать вновь.
На этом, с разбором time intelligence функций в DAX: TOTALYTD, TOTALQTD и TOTALMTD, вычисляющих выражение от начала года, квартала или месяца до текущей даты в рамках текущего контекста в Power BI и PowerPivot, в этой статье все.
Также, напоминаю Вам, что до 24 декабря 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 75% (вместо 20000, всего за 5000 руб.)
В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.
Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 75% (до 24 декабря 2021 г.): узнать подробнее
До конца распродажи осталось:
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи? 
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
до конца распродажи осталось:
до конца распродажи осталось:
Нарастающие итоги в Power Pivot и Power BI
У нас накопились ответы на вопросы о накопительных итогах (даже ответы про накопительные итоги – накапливаются =) Такое впечатление, что с задачей рассчитать нарастающие или накопительные итоги сталкивается практически каждый слушатель наших курсов. И вопрос стоит даже не в том, какую формулу использовать.
Обычно всех интересуют нюансы. Например, как «остановить» нарастающий итог, чтобы он не отображался в периодах, где данных еще нет. Или как считать такой итог не в рамках года, а за все время.
Немного о нарастающих итогах
Нарастающий итог – это сумма показателей, где к данным текущего периода добавляются суммы предыдущих периодов. Вычисления нарастающих итогов обычно просят руководители, чтобы увидеть показатели с начала месяца, квартала или года, например, продажи или прибыль. Или посмотреть, сколько денег принес проект за все время работы. Совсем специфический случай – моделирование расчетных остатков, переходящих из года в год.
Отсюда, вычисления можно разделить на два вида:
а) внутри периода (с начала месяца, квартала, года);
б) без привязки к периодам.
В Power Pivot и Power BI для расчета нарастающих итогов есть специальные формулы.
DAX-формулы для расчета нарастающих итогов
4. Нарастающий итог без привязки к периодам.
При расчете нарастающего итога без привязки к периодам показатели будут суммироваться с самого начала проекта – с его первой даты, а в начале нового периода не «сбросятся».
EBITDA и другие МАТы
Эпиграф: В презентации анализа результатов продаж бренд-менеджер использовал МАТы…
Эта статья для тех медицинских представителей, которые не всегда понимают, о чем говорят менеджеры, когда рассказывают о результатах работы. И для менеджеров, которые думают, что их все понимают…
Очень хорошо помню свои ощущения, когда впервые смотрел такую презентацию. В аудиторию вчерашних врачей и провизоров лился поток терминов, который внушал сомнения, что докладчик говорит по-русски:
Мы оперируем на стагнирующем рынке… На этом графике вы видите динамику первичных продаж в МАТах… Маркет шер падает в деньгах и, особенно, в упаковках… Прескрайберы слишком часто выбирают наших компетиторов, и консьюмеры не получают бенефитс… Мы связываем это с падением шер-оф-войс вследствие неэффективной работы сейлс-форса…
Стало понятно, почему обязательное условие для работы продакт-менеджера – знание английского языка.
Вот только далеко не всегда достаточно выучить английский. Надо при этом еще и русский не забыть.
В общем, начинаем составлять
русско-русский словарь медицинского представителя.
русско-русский словарь менеджера.
Для медицинского представителя объясним некоторые понятия, а для менеджеров предложим варианты более понятных слов.
Начнем с продаж в МАТах.
MAT (эм-эй-ти) – это аббревиатура от английского Moving Annual Total. Плавающая годовая сумма. То есть, продажи в МАТах – это сумма продаж за последние 12 месяцев от выбранного месяца. Проще понять на примере: MAT за март 2014 – это сумма продаж с апреля 2013 по март 2014 включительно; MAT за апрель 2014 – это сумма продаж с мая 2013 по апрель 2014 включительно. То есть, добавили продажи нового месяца и вычли продажи самого «старого». Зачем это нужно – тема для отдельного разговора.
YTD (Year-to-date) – сумма продаж с начала года до выбранного месяца включительно. То есть YTD за март 2014 – это сумма продаж с января по март (3 месяца). А YTD за апрель 2014 – это сумма продаж с января по апрель (4 месяца). И так далее до декабря, где MAT и YTD встретятся и будут одинаковыми – 12 месяцев с января по декабрь одного года.
По аналогии с YTD есть еще QTD (Quarter-to-date) и MTD (Month-to-date) – периоды от первого дня квартала и от первого дня месяца соответственно. Используются реже.
Мы сейчас говорили о продажах, но на самом деле понятия MAT, YTD, QTD, MTD – это период, то есть время. Считать за это время можно не только продажи. А, например, долю рынка:
MS (Market Share) – произносится, как «маркет шер/шеа/шеар», кому как кажется более похоже на английское произношение. По-русски – доля рынка. Характеризует положение компании или препарата среди конкурентов. Измеряется в процентах. Например, если объем продаж всех фармкомпаний на территории 1000000 долларов, а ваш объем продаж на той же территории 15000 долларов, то ваша доля рынка на этой территории = (150000/1000000)*100%=1,5%.
Рынок в этом случае – понятие растяжимое. Предположим, в вашей компании есть цефтриаксон. Можно сравнивать его только с цефтриаксонами других компаний – это будет его доля на рынке цефтриаксонов. Можно смотреть долю рынка среди всех цефалоспоринов, всех антибиотиков и т.д. Считать можно в деньгах или упаковках.
И в зависимости от периода, который выбран, может быть MS в MAT, YTD, QTD, месяц. Более подробно в следующих статьях. Сейчас только одну мысль: «Бренд можно считать успешным, если он растет быстрее рынка и быстрее конкурентов. И, соответственно, доля рынка бренда растет»
Следующий показатель очень интересный:
Share of Voice (SoV) – доля голосов, произносится приблизительно как «шер оф войс». Если быть точнее, то это один показатель, а два. И не доля голосов, а доля воспоминаний. Первый показатель – SoV Promotion, а второй — SoV Prescription. Воспоминания врача о промоции препарата и о назначении препарата.
Определяют эти показатели компании, которые занимаются исследованиями рынка. Приходит их представитель к врачам и спрашивает: «С какими препаратами к вам за последний месяц приходили медицинские представители?» И врачи начинают вспоминать… А потом эти данные собираются вместе и оказывается, что чаще всего врачи вспоминают «препарат А фармкомпании Б». Значит у компании Б самые запоминающиеся медпредставители. И препарат А у врачей первый на уме, а значит будет и первый «на языке», то есть в рецепте. Только необязательно, потому что врачу задают еще один вопрос: «А какие препараты вы назначали за последние 2 недели?» Врачи отвечают, данные подсчитываются, и оказывается, что назначают они чаще всего «препарат В компании Г». А это значит, что медпредставители фармкомпании Г самые эффективные. Как-то так… Очень интересны возможные сочетания SoV Promotion и SoV Prescription. Но трактовка полученных данных – это тоже тема отдельного разговора.
А теперь коротко пройдемся по другим терминам из «речи менеджера»:
Стагнирующий рынок (от слова стагнация, лат. stagnum — стоячая вода) – рынок, который не растет. Расчитывать на рост продаж препарата можно только за счет конкурентов.
Первичные продажи (Sell-in, отгрузки) – это то, что компания-производитель (поставщик) продала (отгрузила) дистрибьюторам.
Вторичные продажи (Sell-out, продажи) – это то, что дистрибьюторы продали аптекам (или больницам)
Прескрайберы (англ. to prescribe – прописывать)– те, кто прописывают препарат. То есть врачи (или провизоры).
Компетитор – (англ.) конкурент
Консьюмер – (англ.) потребитель, в нашм случае — пациент
Бенефитс – (англ.) преимущества
Сейлс-форс – (англ.) отдел продаж (то же, что и филд-форс — «полевая» служба, внешняя служба)
Теперь можете вернуться к началу статьи и перевести речь менеджера с русского на русский.
Те же, кто дочитал до конца, смогут узнать, что такое EBITDA.
EBITDA (сокращенно. от англ. Earnings before Interest, Taxes, Depreciation and Amortization) — аналитический показатель, равный объёму прибыли до уплаты налогов, расходов на амортизацию и процентов по кредитам.
То есть, сколько бы у нашей фармкомпании могло бы быть денег, если бы мы не платили налоги, по кредитам и не тратились на ремонт оборудования. Вот это была бы прибыль!
Во-первых, показатель имеет очень слабое отношение к работе медпредставителя, это относится к показателям работы компании в целом. Так что EBITDA лично вам не грозит.
Во-вторых, показатель очень теоретический и сомнительный в плане реальной оценки финансовой успешности компании.
На сегодня всё. Продолжение следует. Есть еще много русских слов, которые требуют перевода на русский.
Что в финансовой системе означает YTD
Чтобы отслеживать котировки акций, их динамику и прогнозировать ценовые движения, а также осуществлять финансовую отчетность к компании, необходимо рассчитывать и выбирать временные интервалы. Для расчетов финансисты используют специальные временные промежутки по конкретным датам или периодам, например, ежемесячные, ежеквартальные или полугодовые отчеты.
Часто используют динамически изменяющийся временной интервал, который берет свое начало отсчета с первого января текущего года. Данный интервал носит название YEAR TO DATE или сокращенно YTD. Рассмотрим поподробнее, что это такое и как применяется.
Определение и назначение YTD
Year to Date начинает отсчет с начала года и продолжается по настоящее время. Т.е. его правая временная граница изменяется. YTD используется во многих финансовых контекстах, главным образом для записи результатов деятельности в период между датой (эта дата исключается, т.к. сегодняшний день не является полным) и началом года.
В контексте финансов с начала года в финансовых ведомостях часто приводится подробная информация о результатах деятельности компании. Предоставление текущих результатов с начала года, а также результатов с начала года за один или несколько прошлых лет на одну и ту же дату позволяет владельцам, менеджерам, инвесторам и другим заинтересованным сторонам сравнивать текущие результаты компании с результатами прошлых периодов. Подоходный налог работников может быть основан на общей прибыли в налоговом году на сегодняшний день.
Примеры использования YTD
YTD описывает возвращение показателей относительно данного года. Например: с начала года доходность акций составляет 8%. Это означает, что с 1 января текущего года на сегодняшний день акции выросли на 8%.
Сравнение показателей с начала года может ввести в заблуждение, если год только начался или не определена дата контроля. Показатели с начала года более чувствительны к ранним изменениям, чем к поздним.
Пример: для расчета текущего выставления накладной для компании итоговые накладные за каждый предыдущий месяц текущего года добавляются к итоговым накладным за текущий месяц на сегодняшний день.
Пример по продажам
Допустим, компания А торгует товаром бренда Б. анализируется ежемесячное количество проданного товара в течение двух лет. С помощью рассчитанного показателя YTD можно оценить силу тренда и роста или падения спроса на товар. Пример приведен в таблице.
| Продажи по месяцам | 2017, штук | 2018, штук | 2017 YTD, штук | 2018 YTD, штук | Рост относительно прошлого года, % |
| Январь | 50 000 | 60 000 | 50 000 | 60 000 | 20% |
| Февраль | 45 000 | 55 000 | 95 000 | 115 000 | 21% |
| Март | 45 000 | 65 000 | 140 000 | 180 000 | 28% |
| Апрель | 55 000 | 65 000 | 195 000 | 245 000 | 26% |
| Май | 50 000 | 60 000 | 245 000 | 305 000 | 24% |
| Июнь | 55 000 | 70 000 | 300 000 | 375 000 | 25% |
| Июль | 60 000 | 75 000 | 360 000 | 450 000 | 25% |
| Август | 55 000 | 65 000 | 415 000 | 515 000 | 24% |
| Сентябрь | 65 000 | 75 000 | 480 000 | 590 000 | 23% |
| Октябрь | 60 000 | 75 000 | 540 000 | 665 000 | 23% |
| Ноябрь | 60 000 | 80 000 | 600 000 | 745 000 | 24% |
| Декабрь | 70 000 | 85 000 | 670 000 | 830 000 | 24% |
Из данного примера видно, что минимальный рост наблюдался в начале 2019 года (20-21%), а максимальный рост в марте – 28%. Средний рост YTD по сравнению с предыдущим годом составил 24%. Поэтому с помощью YTD можно корректировать план, наблюдать на его выполнением, анализировать спрос и предложение и возможное количество товара, которое понадобится на следующий год.
Пример по акциям
С помощью YTD можно исследовать поведении цен на акции.
| Продажи по месяцам | 2017, рост/падение | 2017 YTD, штук |
| Январь | 1% | 1% |
| Февраль | 2% | 3% |
| Март | -1% | 2% |
| Апрель | 2% | 4% |
| Май | 2% | 6% |
| Июнь | -6% | 0% |
| Июль | -8% | -8% |
| Август | 4% | -4% |
| Сентябрь | 3% | -1% |
| Октябрь | 3% | 2% |
| Ноябрь | -1% | 1% |
| Декабрь | 6% | 7% |
Таким образом, YTD может показать ежемесячную динамику цен на акции, а также годовой рост или снижение стоимости.
Заключение
Year to Date позволяет:
