СЛОЙ — BI-дашборд выручки производства и кафе с прогнозом и планом

Условие

Данные в Производство и Кафе (выручка по дням, по строкам — месяцы, по столбцам — числа дня).

(А) Создать SQL БД из:

1. Направления (Производство, Кафе).
2. Группы кафе.
3. Наименования кафе.
4. Выручка кафе.
5. Выручка производства.
6. Выручка итого.
7. Календарь понедельников года.

(Б) Сделать в Power BI / Tableau дашборд:

1. Табличное отображение выручки: 3 таблицы (Производство, Кафе, Итого), 3 месяца в столбцах, выходные/праздники подсветить, последний столбец — прирост к аналогичной дате прошлого месяца. В подвале — суммы и общий прирост. Условное форматирование (зелёный/красный/жёлтый).
2. Столбчатый график выручки: факт + прогноз (для незавершённого месяца) + месячный план.
3. Линейный график выручки.
4. Табличное отображение по неделям — недели начинаются с первого понедельника месяца (может смещаться); параметр «start week».
5. Столбчатые графики кафе по группам. 6–7. Аналогичные пункты для другой группы кафе.

Решение

(А) Структура SQL БД

CREATE TABLE direction (
  direction_id INT PRIMARY KEY,
  name         TEXT  -- 'Производство', 'Кафе'
);
 
CREATE TABLE cafe_group (
  group_id INT PRIMARY KEY,
  name     TEXT  -- 'К+С', 'В', etc.
);
 
CREATE TABLE cafe (
  cafe_id  INT PRIMARY KEY,
  name     TEXT,
  group_id INT REFERENCES cafe_group
);
 
CREATE TABLE revenue_cafe (
  cafe_id INT REFERENCES cafe,
  date    DATE,
  amount  NUMERIC(15,2),
  PRIMARY KEY (cafe_id, date)
);
 
CREATE TABLE revenue_production (
  date   DATE PRIMARY KEY,
  amount NUMERIC(15,2)
);
 
CREATE VIEW revenue_total AS
SELECT date,
       SUM(amount) AS total_amount
FROM (
  SELECT date, amount FROM revenue_production
  UNION ALL
  SELECT date, amount FROM revenue_cafe
) x
GROUP BY date;
 
CREATE TABLE calendar_mondays AS
SELECT g::date AS monday
FROM generate_series(DATE '2022-01-03', DATE '2024-12-30', INTERVAL '7 day') g;

(Б) Power BI / DAX

1. Таблица 3 месяца + прирост M-1

Revenue =
SUM(revenue[amount])
 
PrevMonthSameDay =
VAR currDay = MAX(calendar[date])
VAR prevDay = EDATE(currDay, -1)   -- та же дата месяцем раньше
RETURN
CALCULATE([Revenue], FILTER(ALL(calendar), calendar[date] = prevDay))
 
Growth =
DIVIDE([Revenue] - [PrevMonthSameDay], [PrevMonthSameDay])
 
GrowthColor =
SWITCH(
  TRUE(),
  [Growth] > 0, "Green",
  [Growth] < 0, "Red",
  "Yellow"
)

Условное форматирование на меру Growth через rules: > 0 → зелёный, < 0 → красный, = 0 → жёлтый.

2. Факт vs прогноз vs план

Факт за дни с заполненной выручкой; прогноз — линейная регрессия / последний месяц; план — отдельная таблица plan(month, target).

IsFact = NOT(ISBLANK(SUM(revenue[amount])))
 
Forecast =
IF([IsFact], [Revenue],
  [LastYearSameDay] * [GrowthRate])

3. Линейный график

Тривиальный line chart: дата × [Revenue] + [Forecast].

4. Недельная таблица

Параметр «start_week_date» (What-if):

WeekIndex =
DATEDIFF([start_week_date], calendar[date], DAY) / 7
 
CurrWeekRevenue =
CALCULATE([Revenue], FILTER(calendar, [WeekIndex] = SELECTEDVALUE(...)))
 
PrevMonth4WeeksAgo =
CALCULATE([Revenue], DATEADD(calendar[date], -28, DAY))
 
WeeklyGrowth =
DIVIDE([CurrWeekRevenue] - [PrevMonth4WeeksAgo], [PrevMonth4WeeksAgo])

Color formatting min/mid/max: используйте Conditional formatting с rules «top color = green, bottom = red, middle = yellow».

Подводные камни

1. Выходные в исходной таблице '-' — нужно конвертировать в NULL.
2. Праздники — отдельный справочник; для условного форматирования нужна таблица holidays.
3. «Прирост к аналогичной дате прошлого месяца»: если в прошлом месяце такой даты нет (например, 31 марта vs 31 февраля) — обработайте как NULL.
4. «Прогноз на незавершённый месяц»: какой алгоритм? Линейная экстраполяция, ARIMA, manual coefficient — нужно уточнять у заказчика.
5. What-if параметр в Power BI: создаётся через New Parameter, генерирует disconnected table.
6. Производственный календарь: "понедельники года" нужно генерировать с учётом локали (RU); ISO week в Power BI — WEEKNUM(...,2).

Эталонный ответ

(А) Реляционная схема: dim_direction, dim_cafe_group, dim_cafe, fact_revenue_cafe, fact_revenue_production, view revenue_total, calendar_mondays. (Б) DAX-меры [Revenue], [PrevMonthSameDay], [Growth], [Forecast]; conditional formatting по правилам цвета; What-if параметр для start_week.