В начале мы определились с параметрами, на основе которых будет строиться кредитный калькулятор - это сумма кредита, срок кредита в месяцах и процентная ставка. Далее перед нами встал вопрос о том, а каким образом внести в нашу модель эти самые показатели? Создать таблицу со всем пересечением сроков, сумм и процентных ставок? Ну уж нет, миллиарды строк пересечения - это слишком. Здесь нам на помощь пришла редко используемая кнопочка создания параметра на панели управления:
При помощи неё мы сформировали три необходимых параметра:
Сумма кредита: числа от 0 до 10 000 000 с шагом в 1 000
Срок кредита: числа от 1 до 360 с шагом в 1
Процентная ставка: числа от 0.001 до 1 с шагом в 0.001
Таким образом у нас появились данные, с которыми можно работать. Но перед этим мы ещё ручками создали таблицу с видами кредита: “Аннуитетный” и “Дифференцированный”. После разместили фильтры на странице отчёта и пошаманили в настройках для соответствия корпоративному цвету:
Теперь перед нами встала задача формирования ежемесячного календаря платежей. То есть на фильтре должен быть срок, а в таблице - все числа от единицы и до конца срока. Тут нам в голову пришла идея создания таблицы дубликата срока кредита, значения которых будут фильтроваться по изначальной таблице. Таким образом у нас появилось пять таблиц:
Создаём визуальный элемент “Таблица” и отправляем в него столбец со сроком, созданным на прошлом шаге:
Как мы видим на данном этапе фильтр никак не влияет на значения в таблице. Теперь нам нужно было создать фильтр который бы оставлял только те числа, которые меньше или равны сроку кредита, а для этого пишем меру:
Month number (Filter) =
VAR MONTH_NUMBER = MAX('Period (filter)'[Month number (Filter)])
VAR NOW_MONTH = FIRSTNONBLANK('Period (table)'[Period (table)],'Period (table)'[Period (table)])
RETURN IF(NOW_MONTH<=MONTH_NUMBER,1,0)
Данная мера для всех значений, которые меньше срока кредита или равны ему, ставит единицу, а для остальных – ноль. Перетаскиваем эту меру в фильтр визуального элемента и ставим условие – данный показатель должен являться единицей:
В таблице остались только те значения, которые нас интересуют.
Далее нужно перейти непосредственно к самим вычислениям. На данном шаге нам очень помогли финансовые функции, которые уже существуют в самом Power BI (https://docs.microsoft.com/ru-ru/dax/financial-functions-dax). Простая логика наших мер будет следующая: Если вид платежа = “Аннуитетный”, то одно вычисление, если “Дифференцированный”, то другое.
Начнём с вычисления суммы погашения тела кредита на каждый месяц платежа. Если для дифференцированного платежа достаточно сумму разделить на срок кредита, то для вычисления аннуитетного пришлось воспользоваться функцией CUMPRINC(), подробнее про которую вы сможете прочитать в вышеуказанном источнике. Таким образом у нас получилась следующая мера:
Credit bodies repayment =
VAR CREDIT_SUM = [Сredit amount]
VAR PERCENTAGE = [Percentage rate (month)]
VAR MAX_MONTH = MAX('Period (filter)'[Month number (Filter)])
VAR NOW_MONTH = FIRSTNONBLANK('Period (table)'[Period (table)],'Period (table)'[Period (table)])
VAR CREDIT_TYPE = MAX('Credit type'[Payment])
RETURN SWITCH(
TRUE(),
CREDIT_TYPE="Аннуитетный" && NOW_MONTH <= MAX_MONTH,
ABS(
CUMPRINC(
PERCENTAGE,
MAX_MONTH,
CREDIT_SUM,
NOW_MONTH,
NOW_MONTH,
0
)
),
CREDIT_TYPE="Дифференцированный" && NOW_MONTH <= MAX_MONTH,
CREDIT_SUM/MAX_MONTH
)
Если вам сложно ориентироваться в мерах или контекстах мер, у нас есть замечательный вебинар на эту тему, для получения доступа к нему вам только нужно связаться с одним из наших сотрудников)
Далее посчитаем сумму погашения процента в месяц, при помощи меры показанной ниже, в ней используются формулы CUMIPMT и ISPMT:
Percentage repayment =
VAR CREDIT_SUM = [Сredit amount]
VAR PERCENTAGE = [Percentage rate (month)]
VAR MAX_MONTH = MAX('Period (filter)'[Month number (Filter)])
VAR NOW_MONTH = FIRSTNONBLANK('Period (table)'[Period (table)],'Period (table)'[Period (table)])
VAR CREDIT_TYPE = MAX('Credit type'[Payment])
RETURN SWITCH(
TRUE(),
CREDIT_TYPE="Аннуитетный" && NOW_MONTH <= MAX_MONTH,
ABS(
CUMIPMT(
PERCENTAGE,
MAX_MONTH,
CREDIT_SUM,
NOW_MONTH,
NOW_MONTH,
0
)
),
CREDIT_TYPE="Дифференцированный" && NOW_MONTH <= MAX_MONTH,
ABS(
ISPMT(
PERCENTAGE,
NOW_MONTH-1,
MAX_MONTH,
CREDIT_SUM
)
))
После считаем сумму ежемесячного платежа при помощи милипусечной меры:
Monthly payment = [Percentage repayment] + [Credit bodies repayment]Разместим это всё дело на странице отчёта:
Для полной картины нам не хватает остатков на начало и конец периода. Меры будут похожи друг на друга как братья Уизли в фильме про Гарри Поттера. Мера для вычисления остатка на конец месяца выглядит следующим образом:
Final balance of the month =
VAR CREDIT_SUM = [Сredit amount]
VAR PERCENTAGE = [Percentage rate (month)]
VAR MAX_MONTH = MAX('Period (filter)'[Month number (Filter)])
VAR CREDIT_TYPE = MAX('Credit type'[Payment])
VAR NOW_MONTH = FIRSTNONBLANK('Period (table)'[Period (table)],'Period (table)'[Period (table)])
VAR CREDIT_TABLE = CALCULATE
(
SUMX(
SUMMARIZE(
FILTER(
'Period (table)',
'Period (table)'[Period (table)] <= NOW_MONTH
),
'Period (table)'[Period (table)],
"Тело",SWITCH(
TRUE(),
CREDIT_TYPE="Аннуитетный" && NOW_MONTH <= MAX_MONTH,
ABS(
CUMPRINC(
PERCENTAGE,
MAX_MONTH,
CREDIT_SUM,
'Period (table)'[Period (table)],
'Period (table)'[Period (table)],
0
)
),
CREDIT_TYPE="Дифференцированный" && NOW_MONTH <= MAX_MONTH,
CREDIT_SUM/MAX_MONTH
)
),
[Тело]
),
ALLSELECTED(
'Period (table)'[Period (table)]
)
)
Return [Сredit amount] - CREDIT_TABLE
Меру для вычисления остатка на начало месяца я не покажу, но скажу лишь одно: её легко получить, преобразовав магическим образом бирюзовый участок кода в мере на вычисление остатка на конец месяца. (УДАЛИТЬ РАВНО)
Помещаем все получившиеся меры в таблицу, немного работаем над дизайном и получаем:
Добавим ещё пару мер, которые будут показывать обобщающую информацию о кредите.
Итоговая стоимость кредита:
Final credit cost =
VAR CREDIT_SUM = [Сredit amount]
VAR PERCENTAGE = [Percentage rate (month)]
VAR MAX_MONTH = MAX('Period (filter)'[Month number (Filter)])
VAR NOW_MONTH = FIRSTNONBLANK('Period (table)'[Period (table)],'Period (table)'[Period (table)])
VAR CREDIT_TYPE = MAX('Credit type'[Payment])
VAR CREDIT_TABLE = CALCULATE(
SUMX(
SUMMARIZE(
FILTER(
'Period (table)',
'Period (table)'[Period (table)] <= MAX_MONTH
),
'Period (table)'[Period (table)],
"Тело", SWITCH(
TRUE(),
CREDIT_TYPE="Аннуитетный" && NOW_MONTH <= MAX_MONTH,
ABS(
CUMIPMT(
PERCENTAGE,
MAX_MONTH,
CREDIT_SUM,
NOW_MONTH,
NOW_MONTH,
0
)
)
+ ABS(
CUMPRINC(
PERCENTAGE,
MAX_MONTH,
CREDIT_SUM,
NOW_MONTH,
NOW_MONTH,
0
)
),
CREDIT_TYPE="Дифференцированный" && NOW_MONTH <= MAX_MONTH,
ABS(
ISPMT(
PERCENTAGE,
MAX('Period (table)'[Period (table)])-1,
MAX_MONTH,
CREDIT_SUM
)
) + CREDIT_SUM/MAX_MONTH
)
),
[Тело]
),
ALLSELECTED(
'Period (table)'[Period (table)]
)
)
Return CREDIT_TABLE
Переплата в процентах:
Percentage overpayment = ([Final credit cost]/[Сredit amount])-1
Переплата в деньгах:
Currency overpayment = [Final credit cost] - [Сredit amount]
Помещаем получившееся меры на страницу отчёта, добиваем ещё одним визуальным элементом и получаем:
Теперь нужно отключить взаимодействие между таблицей и визуальными элементами, расположенными слева от неё, для того чтобы нажатие на строку таблицы не наносило вред психике остальных визуальных элементов. Для этого нажимаем на таблицу, заходим в “Формат” → “Изменить взаимодействия” и ставим знаки “Стоп” на остальные визуальные элементы. Таким образом, у нас получится:
Посмотрим на получившийся отчёт:
*Звуки восторга от возможностей Power BI*.
Ну какая красота, вы посмотрите! :) В итоге у нас получился готовый кредитный калькулятор, а также незабываемый опыт использования мер и параметров. Всем успехов на просторах мира BI.
Понажимать кнопочки на кредитном калькуляторе можно по ссылке.
Если данная статья была полезной, оставьте ниже комментарий)
