Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
- DAX функции TOTALYTD, TOTALQTD, TOTALMTD в Power BI и Power Pivot
- Пример формул на основе time intelligence функций группы TOTAL (YTD, QTD и MTD)
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы разберем несколько функций языка DAX, из категории time intelligence (логики операций с датами и временем), которые вычисляют заданное выражение в текущем периоде в Power BI и Power Pivot. А именно, изучим функции TOTALYTD, TOTALQTD и TOTALMTD, вычисляющие выражение, в соответствии со своим названием, от начала года, квартала или месяца до текущей даты.
Напомню, что в предыдущей статье мы рассматривали похожие функции, но, которые, не вычисляют выражение как TOTALYTD, TOTALQTD и TOTALMTD, а возвращают набор дат, также, для текущего периода.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
Да, и еще один момент, в рамках распродажи до 29 ноября 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)
В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.
Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 29 ноября 2024 г.): узнать подробнее
DAX функции TOTALYTD, TOTALQTD, TOTALMTD в Power BI и Power Pivot
TOTALYTD () — вычисляет заданное выражение от начала года до текущего дня в рамках текущего контекста.
TOTALQTD () — вычисляет заданное выражение от начала квартала до текущего дня в рамках текущего контекста.
TOTALMTD () — вычисляет заданное выражение от начала месяца до текущего дня в рамках текущего контекста.
Синтаксис:
TOTALYTD (Выражение; [Дата]; Фильтр; "Конец Года") TOTALQTD (Выражение; [Дата]; Фильтр) TOTALMTD (Выражение; [Дата]; Фильтр)
Где:
- Выражение — выражение, возвращающее единственное скалярное значение
- [Дата] — столбец из дат или выражений, возвращающих даты
- Фильтр — (необязательный параметр) необходимые фильтры для вычисления выражения
- «Конец Года» — (необязательный параметр) текстовая дата, записанная в виде «01/07» («день/месяц»). Определяет дату окончания года (по умолчанию «31/12»)
! — Для корректной работы функций 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:
Прибыль =
SUMX (
'ПродажиЗатраты';
'ПродажиЗатраты'[Продажи] - 'ПродажиЗатраты'[Затраты]
)
Итоговая Прибыль Год =
TOTALYTD (
[Прибыль];
'спрКалендарь'[Date]
)
Итоговая Прибыль Квартал =
TOTALQTD (
[Прибыль];
'спрКалендарь'[Date]
)
Итоговая Прибыль Месяц =
TOTALMTD (
[Прибыль];
'спрКалендарь'[Date]
) Для начала, мы создали формулу меры [Прибыль] для того, чтобы, с одной стороны, просто разместить текущий размер прибыли в визуализации 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, в этой статье все.
Друзья, хотите изучить полный курс по языку DAX для Power BI и Power Pivot
со скидкой 50% вместо 10000 всего за 5000 р.?
акция действует до 29 ноября
Узнать все подробности об этом курсе, а также заказать его со скидкой 50% всего за 5000 руб. Вы можете на странице курса, кликнув по кнопке ниже (до 29 ноября):
Пожалуйста, оцените статью:
![[Экспресс-видеокурс] Быстрый старт в языке DAX](https://biprosto.ru/wp-content/uploads/2018/08/kurs-free-5.png "Нажмите на ссылку, чтобы записаться в экспресс-курс по DAX")
Успехов Вам, друзья!С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
Как в Power BI (Power Pivot) получить первую (последнюю) известную дату? DAX функции FIRSTDATE и LASTDATE
DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot
DAX функции MAX, MAXA, MAXX и MIN, MINA, MINX (для Power BI и Power Pivot)
А что делать если мне нужен накопленный итог за несколько лет? То есть чтобы функция не обнулялась с каждым новым годом, а суммировала сплошняком.
воспользуйтесь следующим кодом, только названия соответствующих таблиц и столбцов измените на свои:
Накопительные_Затраты = CALCULATE( SUM('Пр_Транзакции'[Затраты]); FILTER( ALLSELECTED('Дата_Оплаты'[Дата]); ISONORAFTER('Дата_Оплаты'[Дата]; MAX('Дата_Оплаты'[Дата]); DESC) ) )Здравствуйте, Антон!
Подскажите, а как лучше всего сделать вывод в матрицу колонки с приращением показателя относительно прошлого года (месяц к месяцу)? Пример — 2017 и 2018 год, надо вывести колонку Продажи (сделана мерой SUM) и вывести колонку «Продажи к прошлому году», в которой вывести 100 * «продажи в текущем месяце»/»Продажи в прошлом году в таком же месяце».
Дмитрий, здравствуйте.
К сожалению, не совсем понятна суть Вашего вопроса. Что именно Вам требуется? Как технически вывести эту колонку в матрице? Или как рассчитать соответствующую формулу?
Для сравнения текущих показателей с предыдущими используйте функции PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH и PREVIOUSDAY (https://biprosto.ru/dax/previous.html) или DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR (https://biprosto.ru/dax/dateadd-parallelperiod-sameperiodlastyear.html)