Россия Нет

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».

 

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, в этой статье все.

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1
(13 голосов, в среднем: 4.6 из 5 баллов)
[Экспресс-видеокурс] Быстрый старт в языке DAX

 
 
Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

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

 
Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Автор статьи:
Категория: Язык функций и формул DAX для Power BI и Excel (Power Pivot)

Добавить комментарий

* Заполняя форму отправки комментария своими персональными данными (имя, email, сайт и др.), Вы автоматически подтверждаете свое согласие на обработку своих персональных данных


  1. Alexandr:

    А что делать если мне нужен накопленный итог за несколько лет? То есть чтобы функция не обнулялась с каждым новым годом, а суммировала сплошняком.

    1. Антон Будуев:

      воспользуйтесь следующим кодом, только названия соответствующих таблиц и столбцов измените на свои:

      Накопительные_Затраты =
      CALCULATE(
          SUM('Пр_Транзакции'[Затраты]);
          FILTER(
              ALLSELECTED('Дата_Оплаты'[Дата]);
              ISONORAFTER('Дата_Оплаты'[Дата]; MAX('Дата_Оплаты'[Дата]); DESC)
          )
      )
      
  2. Дмитрий:

    Здравствуйте, Антон!
    Подскажите, а как лучше всего сделать вывод в матрицу колонки с приращением показателя относительно прошлого года (месяц к месяцу)? Пример — 2017 и 2018 год, надо вывести колонку Продажи (сделана мерой SUM) и вывести колонку «Продажи к прошлому году», в которой вывести 100 * «продажи в текущем месяце»/»Продажи в прошлом году в таком же месяце».

    1. Антон Будуев:

      Дмитрий, здравствуйте.

      К сожалению, не совсем понятна суть Вашего вопроса. Что именно Вам требуется? Как технически вывести эту колонку в матрице? Или как рассчитать соответствующую формулу?

      Для сравнения текущих показателей с предыдущими используйте функции PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH и PREVIOUSDAY (https://biprosto.ru/dax/previous.html) или DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR (https://biprosto.ru/dax/dateadd-parallelperiod-sameperiodlastyear.html)