Материал: Instruction Power BI_20200706T184553_20201222T142516_20210728T121504

Инструкция: как сформировать отчет о дебиторской задолженности в Power BI

Содержание:

1. Формирование архитектуры данных для последующего создания интерактивных диаграмм

1.1. Подготовка исходных массивов данных

1.2. Формирование запросов для подключения массивов исходных данных

1.3. Формирование взаимосвязей таблиц, создание модели данных

1.4. Создание DAX-формул для дополнительных вычислений

2. Формирование и применение интерактивных аналитических отчетов в Power BI

2.1. Создание аналитических визуальных элементов и взаимосвязей между ними

2.2. Последующая актуализация информационных панелей, добавление нового отчетного периода

1. Формирование архитектуры данных для последующего создания интерактивных диаграмм.

1. Подготовка исходных массивов данных

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

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

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

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

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

1.2. Формирование запросов для подключения массивов исходных данных.

Создаем новый файл Power BI. В диалоговом окне выбираем пункт меню «Получить данные».

В качестве источника указывается файл Excel. В стандартном диалоговом окне указываются файлы-источники.

В диалоговом окне «Навигатор» указываются загружаемые таблицы. На нижеследующем скриншоте отражены загружаемые таблицы применяемых классификаторов.

После завершения загрузки подключаемые таблицы отражаются в правой части окна Power BI в разделе «Поля».

(Во избежание потери данных, созданный файл Power BI необходимо сохранить с использованием стандартных пунктов меню: «Файл» -> «Сохранить»).

Последующее подключение источников данных осуществляется с использованием запросов (Power Query).

Последовательность выбора пунктов меню приведена на нижеследующем скриншоте.

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

Для обеспечения возможности анализа динамики показателей, в данном примере смоделированы исходные еженедельные данные за 3 календарных месяца, в связи с чем созданы подключения к каждому из них. По результатам созданных подключений, в разделе «Поля» отображаются соответствующие запросы, названия которых соответствуют названиям таблиц Excel.

1.3. Формирование взаимосвязей таблиц, создание модели данных

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

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

В разделе «Данные» [A] перейти на вкладку «Моделирование» [B] и выбрать пункт «Создать таблицу» [C].

В строке формул ввести формулу, приведенную на нижеследующем скриншоте ([A]-[C]). Для ускорения ввода переменных в формуле целесообразно пользоваться выбором из выпадающих списков, отображаемых автоматически после ввода апострофа («’»). После ввода формулы, в разделе «Поля» отображается новая таблица ([D], [E]).

s

После создания объединенной таблицы необходимо сформировать модель данных, фиксирующую связи между таблицами. Необходимо перейти в раздел «Связи» [A]. Для установления связи между таблицами необходимо «перетащить» (нажать левую кнопку мыши, при нажатой кнопке перевести курсор на связываемую область, отпустить кнопку) поле одной связываемой таблицы [B] на соответствующее поле другой [C], по которому данные таблицы связываются. Например, объединенная таблица выгрузок дебиторской задолженности связывается с классификатором покупателей по полю (столбцу) «Системный код покупателя».

Для отображения типа связи между таблицами необходимо по двойному щелчку мыши на стрелке (см.пункт [A] на скриншоте ниже по тексту) открыть меню «Изменение связи» [C], или выбрать пункт меню «Управление связями» [B]. В соответствующих разделах окна отражаются связываемые таблицы ([D],[F]), столбцы, по которым они связаны ([E],[G]), а также тип связи. Для подключаемых классификаторов, предполагающих уникальность кода покупателя, тип связи должен быть «многие-к-одному», или «один-к-одному», поскольку дубли в классификаторах не допускаются.

Полная схема данных отражена на нижеследующем скриншоте.

1.4. Создание dax-формул для дополнительных вычислений.

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

Power BI содержит возможность осуществления вычислений в соответствии с контекстом фильтрации данных, заданным пользователем на дашборде.

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

Создание дополнительного столбца, содержащего значения числа дней отсрочки по договору.

Как было отражено выше, таблицы классификатора покупателей и объединенная таблица выгрузок связаны по полю «системный код покупателя». Функция DAX, возвращающая значение соответствующего столбца связанной таблицы, работает по аналогии с распространенной в Excel функцией «ВПР» (VLOOKUP).

Последовательность действий приведена на нижеследующем скриншоте. Необходимо в разделе «Данные» [A] выбрать активную таблицу [B], после чего на вкладке «Моделирование» [C] выбрать пункт «Новый столбец» [D], после чего в строке формул ввести соответствующую формулу [E], применив функцию «RELATED» и выбрав из списка столбец связанной таблицы [G]. После завершения ввода формулы [H] в перечне полей активной таблицы сформировано поле «Отсрочка,дн» [I].

Аналогичным образом сформируем дополнительный столбец, определяющий дату наступления обязательств по контракту. Последовательность действий приведена на скриншоте ниже по тексту. Шаги [G],[H] необходимы для корректного отображения значений в поле в формате «Дата».

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

Как правило, «возраст» просроченной дебиторской задолженности, измеряемый в числе дней просрочки, группируется по периодам для последующего анализа структуры просроченной дебиторской задолженности и соответствующих корректирующих действий.

Для того, чтобы произвести группировку ПДЗ по периодам, используется DAX-функция «SWITCH» совместно с функцией «TRUE». В результате в каждой строке указывается диапазон в днях, под который подпадает соответствующая сумма просроченной дебиторской задолженности.

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

«ПДЗ = IF('Выгрузки_ДЗ_(все_даты_отчета)'[Отклонение_от_срока_по_договору,дн]>0;'Выгрузки_ДЗ_(все_даты_отчета)'[Сумма по счету, руб.];0)

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

Для того, чтобы в знаменатель включались все строки (счета), а не только те, по которым возникла просроченная дебиторская задолженность, применяются формула DAX, игнорирующая контекстный фильтр по полю «Период ПДЗ» в знаменателе (кроме выбранной даты отчета). Для данных вычислений создается новая мера: «ДЗ(всего),тыс.руб. = CALCULATE(sum('Выгрузки_ДЗ_(все_даты_отчета)'[Сумма по счету,тыс.руб.]);ALLEXCEPT('Выгрузки_ДЗ_(все_даты_отчета)';'Выгрузки_ДЗ_(все_даты_отчета)'[Дата отчета]))

Последовательность действий и формула приведены на нижеследующем скриншоте.

Для расчета показателя «Доля ПДЗ» создадим соответствующую меру:

«Доля ПДЗ = DIVIDE(SUM('Выгрузки_ДЗ_(все_даты_отчета)'[ПДЗ,тыс.руб.]);'Выгрузки_ДЗ_(все_даты_отчета)'[ДЗ(всего),тыс.руб.])

Во избежание выдачи на информационную панель ошибки деления на ноль, применяется DAX-функция «DIVIDE» [E], которая при делении на ноль выдает пустое значение.

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