Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как показать первый или последний день предыдущего месяца на основе заданной даты в Excel?

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

Содержание статьи: (нажмите для доступа к соответствующей части статьи) :

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI и Power Pivot сдвинуть даты вперед или назад для сравнения текущих показателей с прошлыми или будущими. А конкретно, разберем функции, отвечающие в DAX за этот процесс — DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR.

Давайте рассмотрим каждую функцию более детально.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция DATEADD в Power BI и Power Pivot

DATEADD () — создает таблицу со столбцом из дат, сдвинутых назад (в прошлое) или вперед (в будущее) на заданное количество интервалов от даты текущего контекста.

  • [Дата] — столбец из дат или выражений, возвращающих даты
  • Количество Интервалов — целое число, характеризующее количество интервалов, которое нужно добавить (вычесть) к дате в текущем контексте. Если указано положительное число — то интервалы добавляются, если указано отрицательное число — то интервалы вычитаются
  • Интервал — тип интервала: year (год), quarter (квартал), month (месяц), day (день)

! — Для правильной работы функции необходимо в качестве ее первого параметра [Дата] использовать столбец из таблицы непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.

Мы рассмотрим несколько примеров того, как работает DATEADD с помощью DAX.

В Power BI Desktop имеется таблица с датами «Календарь»:

Календарь дат

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

И для примера того, как она сдвигает даты, создадим в таблице «Календарь» вычисляемый столбец на основе следующей формулы:

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

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

В третьем — тип самого интервала (день).

В результате выполнения этой формулы на основе DATEADD мы получаем сдвинутый столбец дат:

Читайте так же:
Как посчитать, содержит ли ячейка текст или часть текста в Excel?

Результат работы формулы в Power BI на основе DAX функции DATEADD

Другими словами, каждая текущая дата в столбце [Дата] соответствует дате в прошлом 2 дня назад (для текущей даты 5 января, она соответствует прошедшей дате 3 января).

Если в формуле в качестве количества интервалов указано положительное число :

Тогда изменения будут в будущем:

Функция DATEADD - сдвиг даты в будущее

Другими словами, каждой текущей дате в столбце [Дата] соответствует будущая дата на один день вперед (для текущей даты 1 января она соответствует будущей дате 2 января).

DAX функция PARALLELPERIOD в Power BI и Power Pivot

PARALLELPERIOD () — возвращает таблицу из дат, смещенных во времени вперед или назад параллельно текущей дате в текущем контексте на заданное количество интервалов.

  • [Дата] — столбец из дат или выражений, возвращающих даты
  • Количество Интервалов — целое число, характеризующее количество интервалов, которое нужно добавить (вычесть) к дате в текущем контексте. Если указано положительное число — то интервалы добавляются, если указано отрицательное число — то интервалы вычитаются
  • Интервал — тип интервала: year (год), quarter (квартал), month (месяц)

! — Для правильной работы функции необходимо в качестве ее первого параметра [Дата] использовать столбец из таблицы непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.

Функции D ATEADD и PARALLELPERIOD почти идентичны, за исключением того, что:

  1. PARALLELPERIOD не имеет диапазона дней
  2. PARALLELPERIOD возвращает весь параллельный период (например, от начала до конца месяца), тогда как DATEADD возвращает только диапазон, указанный в столбце исходной даты.

Например, изначальный набор дат будет отфильтрован датами от 16 до 25 октября. И если мы обеими функциями попытаемся возвратить набор дат на 1 месяц назад, то DATEADD возвратит даты только с 16 до 25 сентября, а PARALLELPERIOD — уже возвратит даты всего предыдущего месяца от начала и до конца, то есть, с 1 по 30 сентября.

Рассмотрим разницу между DATEADD и PARALLELPERIOD на практическом примере.

Power BI Desktop имеет первую таблицу продаж за 2018 год (с 16.01.2018 по 31.12.2018) :

Исходная таблица продаж

Задача состоит в следующем — в рамках сводной таблицы, напротив текущего месяца, получить сумму продаж за предыдущий месяц. Для этого подойдут обе рассматриваемые выше функции.

Для корректной работы DATEADD и PARALLELPERIOD мы не можем ссылаться на столбец дат, который находится в таблице продаж. Для них нужна совершенно отдельная таблица неразрывных дат «Календарь». В разборе синтаксисов функций я об этом писал (напоминаю, как создавать таблицы неразрывных дат (календари) разбирается в этой статье).

Итак, в модели данных я создал отдельный «Календарь» и связал его со столбцом [Дата Продаж] в таблице «Продажи»:

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

Теперь можно приступить к созданию формул:

Две формулы, которые мы написали выше, вычисляют прибыль за предыдущий месяц:

  • за суммирование отвечает функция SUM, которая суммирует все значения в столбце [СуммаПродаж]
  • за перемещение периода отвечают функции DATEADD и PARALLELPERIOD, которые перемещают даты из календаря на 1 месяц назад, причем, напомню, «Календарь» связан с таблицей «Продажи» по столбцам дат
  • и, за само вычисление суммы, под условием перемещения дат на месяц назад, отвечает DAX функция CALCULATE, внутрь которой помещены все функции, участвующие в работе
Читайте так же:
Как переместить последнее слово в следующую соседнюю ячейку?

Теперь посмотрим, как эти формулы ведут себя в Power BI Desktop:

Результат работы формул в Power BI Desktop на основе DAX функций DATEADD и PARALLELPERIOD

Как мы видим, формулы сработали как нужно, и для текущего месяца (например, февраля) выдают сумму продаж за предыдущий месяц (январь).

Обе функции DATEADD и PARALLELPERIOD пока что работают одинаково.

А различие в том, что DATEADD — возвращает дату, которая была месяц назад. А PARALLELPERIOD весь период, который был месяц назад (в данном случае, период равен месяцу, так как в параметрах указан MONTH).

Давайте это подтвердим на нашем примере и в созданной матрице спустимся в иерархии дат до дней, тогда мы тут же заметим разницу в работе этих двух функций (для удобства демонстрации я создал две копии визуализаций: на первой показан январь по дням, на второй — февраль по дням):

Различие функций DATEADD и PARALLELPERIOD

Это означает, что теперь, когда мы ограничены отображением дней в матрице, для каждого текущего дня функция DATEADD возвращает сумму продаж за тот же день, но за предыдущий месяц. В отличие от этого, функция PARALLELPERIOD возвращает для каждого текущего дня не сумму продаж за тот же день из предыдущего месяца, а сумму продаж за весь предыдущий период (в данном случае месяц).

DAX функция SAMEPERIODLASTYEAR в Power BI и Power Pivot

SAMEPERIODLASTYEAR () — возвращает таблицу из дат, смещенных на 1 год назад относительно дат текущего контекста.

В действительности SAMEPERIODLASTYEAR — это просто упрощенная версия функции DAX DATEADD, а именно упрощенная версия с определенными параметрами:

И эта функция нужна только для того, чтобы упростить написание кода DAX в Power BI.

Давайте посмотрим, как формулы на основе этих двух функций работают на практике:

Работа формул в Power BI на основе DAX функций DATEADD и SAMEPERIODLASTYEAR

Как мы видим, формула на основе SAMEPERIODLASTYEAR, как и формула на основе DATEADD, выводит значение суммы продаж за предыдущий год в январе. Поэтому в случаях, когда нам нужно рассчитать некоторые значения за предыдущий год, лучше просто использовать SAMEPERIODLASTYEAR, так как это позволяет быстрее и проще написать код DAX в Power BI.

В этой статье рассматриваются функции DAX DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR, которые перемещают период данных в прошлое или будущее в Power BI и Power Pivot.

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

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

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

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

Если у вас возникли вопросы по материалу, изложенному в этой статье, вы можете задать их в комментариях ниже. Я постараюсь ответить на них. Вы можете просто разместить там свое мнение, и я с удовольствием его рассмотрю.

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

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

Что еще посмотреть / почитать?

DAX функция AVERAGE

Среднее значение в DAX: функции AVERAGE, AVERAGEA и AVERAGEX в Power BI и Power Pivot

Читайте так же:
Как преобразовать гиперссылку в обычный текст в Excel?

Вывод таблицы ТОП через меру

Как вычислить таблицу Top 3 в Power BI с помощью мер вместо вычисляемых таблиц?

DAX функции DATE, DATEVALUE, YEAR, MONTH и DAY

Функции даты в DAX: DATE, DATEVALUE, YEAR, MONTH, DAY и TODAY в Power BI и Power Pivot

Глава 24. Динамический календарь в Power Query

Это продолжение перевода книги Кена Пульса и Мигеля Эскобара. Язык M для Power Query. Рекомендуется читать главы последовательно, поскольку они не являются независимыми.

С помощью Power Query довольно легко создать полностью динамический календарь в Excel. Используя идеи, изложенные в главе 23, вы можете управлять им на основе значений в двух ячейках Excel. Вам необходимо выполнить четыре шага:

  1. Добавьте в Excel таблицу параметров для хранения календарных ограничений.
  2. Подключите функцию fnGetParameter для передачи ограничений в Power Query.
  3. Создайте базовый столбец календаря с датами.
  4. Добавьте в календарь дополнительные колонки (месяц, квартал, день недели, …).

Ris. 24.1. Tablitsa parametrov

Рис. 24.1. Таблица параметров

Скачать записку в формате Word или PDF, примеры в архиве

Добавление таблицы параметров

Создайте новую книгу Excel, а в ней Таблицу, как показано на рис. 24.1. Таблица должна называться Parameters. Заголовки столбцов должны иметь те же имена, что и на рисунке. Стартовая дата календаря = 01.01.2014. Конечная дата = последней дате текущего месяца, и вычисляется по формуле =КОНМЕСЯЦА(СЕГОДНЯ();0). Синтаксис функции: КОНМЕСЯЦА(нач_дата;число_месяцев), где число_месяцев – количество месяцев до или после даты «нач_дата». Положительное значение аргумента «число_месяцев» означает будущие даты; отрицательное значение – прошедшие даты. Если вы используете 0, функция вернет последний день текущего месяца. Если вы укажете -1, это будет конец предыдущего месяца. 1 соответствует последней дате следующего месяца.

Функция fnGetParameter

Откройте файл fnGetParameter.txt. Скопируйте его содержимое в буфер обмена. Вернитесь к рабочей книге Excel, перейдите в раздел Data -> Get Data -> From Other Sources -> Blank Query. В редакторе Power Query перейдите на вкладку Home -> Advanced Editor. Выделите весь код в окне и нажмите Ctrl+V -> Готово. Переименуйте запрос fnGetParameter.

Ris. 24.2. Funktsiya fnGetParameter gotova k rabote

Рис. 24.2: Функция fnGetParameter готова к выполнению.

Построение структуры календаря

Продолжим. Создайте новый запрос не выходя из редактора Power Query. Главная –> Создать источник –> Другие источники –> Пустой запрос. Переименуйте запрос Calendar. Самый простой способ начать строить календарь – создать простой список. Введите в строке формул: =<1..10>. Нажмите Enter. Вы создали список от одного до десяти:

Ris. 24.3. Teper u vas est prostoj spisok

Рисунок 24.3: Теперь у нас есть простой список

Рассмотрите возможность преобразования этого списка в таблицу и изменения типа данных на дату. Чтобы преобразовать список в таблицу, нажмите Инструменты списка -> Преобразовать -> В таблицу. Оставьте настройки по умолчанию и нажмите Ок. Щелкните правой кнопкой мыши столбец Column1 -> Изменить тип -> Дата. Щелкните правой кнопкой мыши столбец Column1 и выберите Переименовать -> Дата. Хотя это не тот диапазон дат, который вам нужен, вы получили фрагмент календаря:

Ris. 24.4. Kalendar. Pravda nemnogo ustarevshij

Рисунок 24.4 Расписание; правда, немного устаревшее))

Теперь нужно вставить пару шагов в начале запроса, чтобы извлечь даты начала и окончания, а затем передать эти даты в список вместо «от 1 до 10». Перейдите на вкладку Главная –> Расширенный редактор. После строки let добавьте две строки кода M:

Читайте так же:
Как пакетно конвертировать файлы Excel в PDF в Excel?

startdate = fnGetParameter( » Start Date » ),

enddate = fnGetParameter( » End Date » ),

Не забывайте о запятых в конце каждой строки. Нажмите кнопку «Готово». Убедитесь, что оба шага возвращают «правильные» даты:

Ris. 24.5. Start Date vozvrashhaet 01.01.2014

Рис. 24.5. Возврат даты начала 01.01.2014

Переменные должны быть заменены на числа 1 и 10. Выберите шаг «Источник». Измените формулу:

Нажмите Enter. К сожалению, возвращается ошибка:

Ris. 24.6. CHto ne tak

Рис. 24.6. Что не так?

Это сообщение об ошибке звучит не очень понятно. Я бы предпочел, чтобы Power Query сказал: «Оператор недоступен. Он может быть помещен только между двумя числами (двумя точками). Хотя в Excel даты являются целыми числами, Power Query не воспринимает их как таковые. Используя Power Query, преобразуйте даты в числа явным образом. Вернитесь в расширенный редактор. Заполните строки определения переменных:

startdate = Number.From(fnGetParameter( » Start Date » )),

enddate = Number.From(fnGetParameter( » End Date » )),

Нажмите Готово. Перейдите к шагу начальных данных. Дата отображается как целое число 41640. Перейдите к последнему шагу запроса — вы увидите таблицу с 1 января 2014 года:

Ris. 24.7. Kalendar gotov k ispolzovaniyu

Рис. 24.7: Календарь готов к использованию

Добавление столбцов календаря

Выберите столбец Дата -> Добавить столбец -> Дата -> Год -> Год. Выберите столбец Дата -> Добавить столбец -> Дата -> Квартал -> Квартал года. Выберите Дата -> Добавить столбец -> Дата -> Месяц -> Месяц. Выберите столбец Дата -> Добавить столбец -> Дата -> День -> День недели.

Ris. 24.8. V kalendar dobavleny obychnye stolbtsy

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

Функции даты в Excel и Power Query

Ris. 24.9. Primery funktsij Excel

Рисунок 24.9. Примеры функций Excel

Ris. 24.10. Sravnenie funktsij v Excel i Power Query

Рис. 24.10. Сравнение функций в Excel и Power Query

Сложение дат

Добавьте число к дате, и вы будете разочарованы. Несмотря на то, что даты являются числовыми типами данных, Power Query не может неявно преобразовывать их, как Excel. Поэтому Power Query содержит функции для добавления дат:

Ris. 24.11. Funktsii slozheniya dat v Excel i Power Query

Рисунок 24.11: Добавление функций для работы с данными в Excel и Power Query

Даты как текст

Excel использует функцию TEXT() для возврата дат в текстовом формате. В Power Query функция Date.ToText() служит той же цели. С последним пунктом нужно быть осторожным: не только имя функции чувствительно к регистру, но и ее параметры. Поскольку Power Query не русифицирован, он возвращает текст на английском языке:

Ris. 24.12. Daty kak tekst

Рис. 24.12. Даты в виде текста; чтобы увеличить изображение, щелкните правой кнопкой мыши и выберите Открыть изображение в новой вкладке

Как рассчитать последний день месяца в Excel

Часто в процессе работы с датами возникает необходимость динамически определить дату и последний день недели в месяце. Для автоматизации некоторых задач в Excel необходимо динамически получить дату последнего месяца, особенно февраля, и определить день недели, чтобы можно было просто посмотреть на обычный календарь. Ведь в високосном году (366) месяц февраль имеет 29 дней, а в обычном году — 28.

Читайте так же:
Как переместить знак минус справа налево / назад вперед в Excel?

Сколько дней в феврале для любого года и любой даты?

В первую очередь, рассмотрим, как определить, является ли любой данный год високосным, вычислив последний день февраля:

Сколько дней в феврале.

Анализируя пример на этом рисунке, помните, что эта формула может быть применена к любому месяцу для получения даты последнего дня, а не только к февралю. В противном случае она выглядела бы следующим образом:

Функция DATE позволяет генерировать дату «на лету» с помощью трех аргументов:

  1. Год — любое число только от 1900 до 9999.
  2. Месяц — любое положительное или отрицательное число.
  3. День — любое положительное или отрицательное число.

Например, если в качестве номера дня в третьем аргументе указано число 0, Excel вычислит дату последнего дня предыдущего месяца. В качестве наглядного примера приведем формулу с числом 0, которая возвращает 29 февраля 2000 года:

В качестве альтернативы использованию статических значений при определении первых 2 аргументов функции DATE (год и месяц), в следующем примере формулы функция YEAR используется для возврата количества лет от исходных дат. Функция MONTH используется для возврата номера месяца исходной даты. В формуле мы добавляем к этому числу цифру 1, что дает нам номер следующего месяца. Поскольку третий аргумент содержит ноль, мы автоматически получаем дату последнего дня интересующего нас месяца.

Какой день недели для последнего дня месяца?

На рисунке ниже показано, как вычислить последний день февраля для любого года на основе дня недели:

Какой день недели.

Формула состоит из двух частей, разделенных соединяющим оператором амперсантом (&» — «&):

  1. В первой части функция TEXT преобразует числовое значение, возвращаемое функцией DATE, в формат даты. Это указывается во втором аргументе функции — «дд.мм.гггг».
  2. Во второй части функция TEXT устанавливает день недели для исходной даты в своем первом аргументе, используя формат ячеек «ddddddd», указанный во втором аргументе.

Пример использования функции КОНМЕСЯЦА

В новых версиях Excel есть функция СЧЕТЧИК, которая служит альтернативным решением вышеприведенной формулы:

КОНМЕСЯЦА.

Используя функцию CONMOND, вы можете определить дату для текущего, предыдущего или будущего месяца, используя всего два аргумента:

  1. Start_date — дата начала или ссылка на нее.
  2. Number_of_months — количество месяцев от исходной даты. Это может быть любое положительное или отрицательное число. Примеры заполненных аргументов для функции CONMESCENT, возвращающей дату 29 февраля 2000 года:

Если в качестве второго аргумента указать отрицательное число, то функция возвращает дату последнего дня предыдущего месяца:

Если мы укажем во втором аргументе число 0, то, очевидно, получим нужное значение данных для последнего дня текущего месяца:

Как определить високосный год или нет в Excel?

Существует несколько способов определить, является ли год високосным в Excel. Вот некоторые из них:

определить високосный год.

При работе с датами особую сложность представляет тот факт, что каждые четыре года в году 366 дней, тогда как обычно в году 365 дней. Поэтому иногда приходится использовать приведенные выше примеры и формулы для различных расчетов в Excel.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector