Return on equity (ROE): formula and example.

WITH profit AS (
  SELECT SUM(CASE WHEN account_group = 'revenue' THEN amount ELSE -amount END) AS net_profit
  FROM gl_entries
  WHERE account_group IN ('revenue', 'cogs', 'personnel', 'other_opex', 'depreciation', 'interest', 'tax')
    AND entry_date > current_date - interval '12 months'
), equity AS (
  SELECT SUM(amount) FILTER (WHERE entry_date <= current_date) AS equity_now,
         SUM(amount) FILTER (WHERE entry_date <= current_date - interval '12 months') AS equity_prev
  FROM gl_entries
  WHERE account_group = 'equity'
)
SELECT profit.net_profit,
       (equity.equity_now + equity.equity_prev) / 2 AS avg_equity,
       ROUND(100.0 * profit.net_profit / NULLIF((equity.equity_now + equity.equity_prev) / 2, 0), 1) AS roe_pct
FROM profit, equity;

Net Profit =
CALCULATE(SUM(GL[Amount]), GL[AccountGroup] = "revenue")
    - CALCULATE(SUM(GL[Amount]), GL[AccountGroup] IN {"cogs", "personnel", "other_opex", "depreciation", "interest", "tax"})

ROE % =
VAR EquityEnd = CALCULATE(SUM(GL[Amount]), GL[AccountGroup] = "equity", FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))
VAR EquityStart = CALCULATE(SUM(GL[Amount]), GL[AccountGroup] = "equity", FILTER(ALL('Date'), 'Date'[Date] < MIN('Date'[Date])))
RETURN DIVIDE([Net Profit], (EquityStart + EquityEnd) / 2)