Here is a table of Financial Analysis Concepts and Queries – Excel Formulas, DAX and Oracle SQL Queries, searchable and sortable by keywords.
| Category | Subcategory | Concept | Description | Shortcode | Keywords | Excel Formula | DAX Query | Oracle SQL Query |
|---|---|---|---|---|---|---|---|---|
| Core Financial Statements | Income Statement | Revenue | Total income generated from business operations before any expenses are deducted. | sales, top line, income, turnover, gross receipts, P&L | =SUM(Revenue_Range) | Revenue = SUM(FactSales[SalesAmount]) | SELECT SUM(amount) AS revenue FROM transactions WHERE type = ‘revenue’ AND EXTRACT(YEAR FROM txn_date) = :yr | |
| Core Financial Statements | Income Statement | Gross Profit | Revenue minus cost of goods sold (COGS); measures production efficiency. | GP | COGS, cost of goods sold, production cost, gross earnings, margin | =Revenue-COGS | Gross Profit = [Revenue] – [COGS] | SELECT SUM(revenue) – SUM(cogs) AS gross_profit FROM financials WHERE fiscal_year = :yr |
| Core Financial Statements | Income Statement | Operating Income | Gross profit minus operating expenses; profit from core business operations. | EBIT | EBIT, operating profit, income from operations, core earnings, opex | =GrossProfit-OperatingExpenses | EBIT = [Gross Profit] – SUM(FactOpEx[Amount]) | SELECT SUM(gross_profit) – SUM(opex) AS ebit FROM financials WHERE fiscal_year = :yr |
| Core Financial Statements | Income Statement | Net Income | The bottom line – profit after all expenses, taxes, and interest. | NI | bottom line, net profit, earnings, after-tax income, net earnings | =EBIT-Interest-Taxes | Net Income = [EBIT] – [Interest Expense] – [Tax Expense] | SELECT SUM(ebit) – SUM(interest_exp) – SUM(tax_exp) AS net_income FROM financials WHERE fiscal_year = :yr |
| Core Financial Statements | Income Statement | EBITDA | Earnings Before Interest, Taxes, Depreciation, and Amortization; proxy for cash operating performance. | EBITDA | operating performance, cash proxy, depreciation, amortization, interest, taxes, leverage | =EBIT+Depreciation+Amortization | EBITDA = [EBIT] + SUM(FactDA[Depreciation]) + SUM(FactDA[Amortization]) | SELECT SUM(ebit) + SUM(depreciation) + SUM(amortization) AS ebitda FROM financials WHERE fiscal_year = :yr |
| Core Financial Statements | Income Statement | EPS | Earnings Per Share – net income divided by shares outstanding. | EPS | earnings per share, diluted EPS, basic EPS, shares outstanding, stock valuation | =NetIncome/SharesOutstanding | EPS = DIVIDE([Net Income], [Shares Outstanding]) | SELECT net_income / shares_outstanding AS eps FROM financials f JOIN equity e ON f.company_id = e.company_id WHERE f.fiscal_year = :yr |
| Core Financial Statements | Balance Sheet | Assets | Resources owned by the company (cash, receivables, inventory, PP&E, intangibles). | PP&E, receivables, inventory, cash, intangibles, total assets, fixed assets, current assets | =SUM(AssetRange) | Total Assets = SUM(BalanceSheet[Amount]) | SELECT SUM(amount) AS total_assets FROM balance_sheet WHERE account_type = ‘Asset’ AND period_end = :dt | |
| Core Financial Statements | Balance Sheet | Liabilities | Obligations owed to creditors (accounts payable, debt, deferred revenue). | accounts payable, debt, deferred revenue, obligations, creditors, current liabilities, long-term debt | =SUM(LiabilityRange) | Total Liabilities = CALCULATE(SUM(BalanceSheet[Amount]), BalanceSheet[Type]=”Liability”) | SELECT SUM(amount) AS total_liabilities FROM balance_sheet WHERE account_type = ‘Liability’ AND period_end = :dt | |
| Core Financial Statements | Balance Sheet | Equity | Residual interest in assets after liabilities are deducted; shareholder value. | shareholder equity, book value, stockholders equity, retained earnings, net assets | =TotalAssets-TotalLiabilities | Equity = [Total Assets] – [Total Liabilities] | SELECT SUM(amount) AS equity FROM balance_sheet WHERE account_type = ‘Equity’ AND period_end = :dt | |
| Core Financial Statements | Balance Sheet | Working Capital | Current assets minus current liabilities; measures short-term financial health. | WC | liquidity, short-term, current ratio, cash management, operating cycle | =CurrentAssets-CurrentLiabilities | Working Capital = [Current Assets] – [Current Liabilities] | SELECT SUM(CASE WHEN account_type=’Current Asset’ THEN amount ELSE 0 END) – SUM(CASE WHEN account_type=’Current Liability’ THEN amount ELSE 0 END) AS working_capital FROM balance_sheet WHERE period_end = :dt |
| Core Financial Statements | Cash Flow Statement | Operating Cash Flow | Cash generated from core business operations; excludes investing and financing. | OCF | cash from operations, cash generation, operating activities, indirect method, direct method | =NetIncome+Depreciation+/-WorkingCapitalChanges | OCF = SUM(CashFlow[Amount]) | SELECT SUM(amount) AS ocf FROM cash_flow WHERE activity_type = ‘Operating’ AND fiscal_year = :yr |
| Core Financial Statements | Cash Flow Statement | Free Cash Flow | Operating cash flow minus capital expenditures; cash available to distribute or reinvest. | FCF | cash generation, reinvestment, dividends, buybacks, capital allocation, unlevered | =OCF-CapEx | FCF = [OCF] – [CapEx] | SELECT SUM(CASE WHEN activity_type=’Operating’ THEN amount ELSE 0 END) – ABS(SUM(CASE WHEN activity_type=’CapEx’ THEN amount ELSE 0 END)) AS fcf FROM cash_flow WHERE fiscal_year = :yr |
| Core Financial Statements | Cash Flow Statement | CapEx | Capital Expenditures – funds used to acquire or maintain long-term physical assets. | CapEx | capital expenditure, PP&E, fixed assets, investment, infrastructure, maintenance capex, growth capex | =SUMIF(CashFlowType,”CapEx”,AmountRange) | CapEx = CALCULATE(SUM(CashFlow[Amount]), CashFlow[Type]=”CapEx”) | SELECT ABS(SUM(amount)) AS capex FROM cash_flow WHERE activity_type = ‘CapEx’ AND fiscal_year = :yr |
| Core Financial Statements | Cash Flow Statement | OpEx | Operating Expenditures – recurring costs for day-to-day business operations. | OpEx | operating expenses, recurring costs, SG&A, rent, salaries, overhead, day-to-day | =SUMIF(ExpenseType,”Operating”,AmountRange) | OpEx = CALCULATE(SUM(FactExpenses[Amount]), FactExpenses[Type]=”Operating”) | SELECT SUM(amount) AS opex FROM expenses WHERE expense_type = ‘Operating’ AND fiscal_year = :yr |
| Financial Ratios | Profitability | Gross Margin | Gross profit divided by revenue; percentage of revenue retained after direct costs. | GM% | margin percentage, profitability, COGS efficiency, pricing power, gross profit ratio | =GrossProfit/Revenue | Gross Margin % = DIVIDE([Gross Profit], [Revenue]) | SELECT (SUM(gross_profit) / NULLIF(SUM(revenue), 0)) * 100 AS gross_margin_pct FROM financials WHERE fiscal_year = :yr |
| Financial Ratios | Profitability | Net Profit Margin | Net income divided by revenue; overall profitability percentage. | NPM | net margin, bottom line percentage, profitability ratio, after-tax margin | =NetIncome/Revenue | Net Margin % = DIVIDE([Net Income], [Revenue]) | SELECT (SUM(net_income) / NULLIF(SUM(revenue), 0)) * 100 AS net_margin_pct FROM financials WHERE fiscal_year = :yr |
| Financial Ratios | Profitability | Return on Assets | Net income divided by total assets; how efficiently assets generate profit. | ROA | asset efficiency, profitability, utilization, return, performance metric | =NetIncome/AverageAssets | ROA = DIVIDE([Net Income], AVERAGE(BalanceSheet[Total Assets])) | SELECT (net_income / NULLIF(avg_assets, 0)) * 100 AS roa FROM (SELECT f.net_income, AVG(b.total_assets) AS avg_assets FROM financials f JOIN balance_sheet_summary b ON f.company_id = b.company_id AND f.fiscal_year = b.fiscal_year WHERE f.fiscal_year = :yr GROUP BY f.net_income) |
| Financial Ratios | Profitability | Return on Equity | Net income divided by shareholder equity; return generated on equity investment. | ROE | shareholder return, equity efficiency, DuPont, investor return, book value | =NetIncome/AverageEquity | ROE = DIVIDE([Net Income], AVERAGE(BalanceSheet[Equity])) | SELECT (f.net_income / NULLIF(AVG(b.equity), 0)) * 100 AS roe FROM financials f JOIN balance_sheet_summary b ON f.company_id = b.company_id WHERE f.fiscal_year = :yr GROUP BY f.net_income |
| Financial Ratios | Profitability | Return on Invested Capital | NOPAT divided by invested capital; measures efficiency of capital allocation. | ROIC | capital allocation, NOPAT, invested capital, value creation, WACC spread | =NOPAT/(TotalDebt+TotalEquity) | ROIC = DIVIDE([NOPAT], [Debt] + [Equity]) | SELECT (nopat / NULLIF(total_debt + total_equity, 0)) * 100 AS roic FROM financials_detail WHERE fiscal_year = :yr |
| Financial Ratios | Liquidity | Current Ratio | Current assets divided by current liabilities; ability to cover short-term obligations. | CR | liquidity, short-term solvency, current assets, current liabilities, cash coverage | =CurrentAssets/CurrentLiabilities | Current Ratio = DIVIDE([Current Assets], [Current Liabilities]) | SELECT SUM(CASE WHEN account_type=’Current Asset’ THEN amount END) / NULLIF(SUM(CASE WHEN account_type=’Current Liability’ THEN amount END), 0) AS current_ratio FROM balance_sheet WHERE period_end = :dt |
| Financial Ratios | Liquidity | Quick Ratio | Liquid assets (ex-inventory) divided by current liabilities; more conservative liquidity measure. | QR | acid test, liquidity, inventory excluded, liquid assets, cash equivalents | =(CurrentAssets-Inventory)/CurrentLiabilities | Quick Ratio = DIVIDE([Current Assets] – [Inventory], [Current Liabilities]) | SELECT (SUM(CASE WHEN account_type=’Current Asset’ THEN amount END) – SUM(CASE WHEN account_subtype=’Inventory’ THEN amount END)) / NULLIF(SUM(CASE WHEN account_type=’Current Liability’ THEN amount END), 0) AS quick_ratio FROM balance_sheet WHERE period_end = :dt |
| Financial Ratios | Liquidity | Cash Ratio | Cash and equivalents divided by current liabilities; strictest liquidity measure. | cash coverage, most conservative, liquidity stress test, cash equivalents, solvency | =CashAndEquivalents/CurrentLiabilities | Cash Ratio = DIVIDE([Cash], [Current Liabilities]) | SELECT SUM(CASE WHEN account_subtype=’Cash’ THEN amount END) / NULLIF(SUM(CASE WHEN account_type=’Current Liability’ THEN amount END), 0) AS cash_ratio FROM balance_sheet WHERE period_end = :dt | |
| Financial Ratios | Leverage | Debt-to-Equity Ratio | Total debt divided by shareholder equity; measures financial leverage. | D/E | leverage, capital structure, financial risk, debt load, gearing ratio | =TotalDebt/TotalEquity | D/E Ratio = DIVIDE([Total Debt], [Total Equity]) | SELECT SUM(CASE WHEN account_subtype=’Debt’ THEN amount END) / NULLIF(SUM(CASE WHEN account_type=’Equity’ THEN amount END), 0) AS de_ratio FROM balance_sheet WHERE period_end = :dt |
| Financial Ratios | Leverage | Interest Coverage Ratio | EBIT divided by interest expense; ability to service debt obligations. | ICR | debt service, interest expense, EBIT, solvency, times interest earned | =EBIT/InterestExpense | Interest Coverage = DIVIDE([EBIT], [Interest Expense]) | SELECT SUM(ebit) / NULLIF(ABS(SUM(interest_exp)), 0) AS interest_coverage FROM financials WHERE fiscal_year = :yr |
| Financial Ratios | Leverage | Debt-to-EBITDA | Total debt divided by EBITDA; how many years of earnings to pay off debt. | leverage multiple, credit analysis, debt capacity, loan covenants, net debt | =TotalDebt/EBITDA | Debt/EBITDA = DIVIDE([Total Debt], [EBITDA]) | SELECT total_debt / NULLIF(ebitda, 0) AS debt_to_ebitda FROM financials_detail WHERE fiscal_year = :yr | |
| Financial Ratios | Efficiency | Days Sales Outstanding | Average receivables divided by daily revenue; how quickly customers pay. | DSO | receivables, collections, cash conversion, accounts receivable, payment terms | =(AccountsReceivable/Revenue)*365 | DSO = DIVIDE([Accounts Receivable], [Revenue]) * 365 | SELECT (AVG(accounts_receivable) / NULLIF(SUM(revenue) / 365, 0)) AS dso FROM financials f JOIN balance_sheet_summary b ON f.company_id = b.company_id AND f.fiscal_year = b.fiscal_year WHERE f.fiscal_year = :yr |
| Financial Ratios | Efficiency | Days Payable Outstanding | Average payables divided by daily COGS; how long the company takes to pay suppliers. | DPO | accounts payable, supplier terms, cash conversion cycle, payables management | =(AccountsPayable/COGS)*365 | DPO = DIVIDE([Accounts Payable], [COGS]) * 365 | SELECT (AVG(accounts_payable) / NULLIF(SUM(cogs) / 365, 0)) AS dpo FROM financials f JOIN balance_sheet_summary b ON f.company_id = b.company_id AND f.fiscal_year = b.fiscal_year WHERE f.fiscal_year = :yr |
| Financial Ratios | Efficiency | Inventory Turnover | COGS divided by average inventory; how quickly inventory is sold. | inventory management, stock turnover, supply chain, days inventory outstanding, COGS | =COGS/AverageInventory | Inventory Turnover = DIVIDE([COGS], AVERAGE(BalanceSheet[Inventory])) | SELECT SUM(cogs) / NULLIF(AVG(inventory), 0) AS inventory_turnover FROM financials f JOIN balance_sheet_summary b ON f.company_id = b.company_id AND f.fiscal_year = b.fiscal_year WHERE f.fiscal_year = :yr | |
| Financial Ratios | Efficiency | Asset Turnover | Revenue divided by total assets; efficiency of assets in generating revenue. | asset utilization, efficiency ratio, revenue per asset, DuPont, productivity | =Revenue/AverageAssets | Asset Turnover = DIVIDE([Revenue], AVERAGE(BalanceSheet[Total Assets])) | SELECT SUM(revenue) / NULLIF(AVG(total_assets), 0) AS asset_turnover FROM financials f JOIN balance_sheet_summary b ON f.company_id = b.company_id AND f.fiscal_year = b.fiscal_year WHERE f.fiscal_year = :yr | |
| Valuation | Intrinsic Value | Discounted Cash Flow | Values a company by projecting future free cash flows and discounting to present value using WACC. | DCF | intrinsic value, present value, future cash flows, terminal value, WACC, discount rate | =NPV(WACC, FCF_Range)+TerminalValue/(1+WACC)^Years | DCF Value = SUMX(Projections, [FCF] / (1 + [WACC]) ^ [Year]) | SELECT SUM(fcf / POWER(1 + wacc, yr)) AS dcf_value FROM fcf_projections WHERE company_id = :co |
| Valuation | Intrinsic Value | Net Present Value | Present value of future cash inflows minus initial investment; positive NPV = value-creating. | NPV | project valuation, investment decision, time value of money, discount rate, capital budgeting | =NPV(DiscountRate, CashFlowRange)-InitialInvestment | NPV = SUMX(CashFlows, [CashFlow] / (1 + [Rate]) ^ [Period]) – [InitialInvestment] | SELECT SUM(cash_flow / POWER(1 + discount_rate, period)) – initial_investment AS npv FROM project_cashflows WHERE project_id = :proj |
| Valuation | Intrinsic Value | Internal Rate of Return | Discount rate that makes NPV equal to zero; used to evaluate investment return. | IRR | return rate, hurdle rate, break-even discount rate, capital budgeting, project return | =IRR(CashFlowRange) | — Not natively in DAX; use iterative approximation or R/Python visual | — Not natively in SQL; typically computed in Python/Excel and stored as result |
| Valuation | Intrinsic Value | WACC | Weighted Average Cost of Capital – blended rate of equity and debt cost; used as DCF discount rate. | WACC | cost of capital, discount rate, cost of equity, cost of debt, capital structure, hurdle rate | =(E/(D+E))*Ke+(D/(D+E))*Kd*(1-TaxRate) | WACC = DIVIDE([Equity], [Equity] + [Debt]) * [Cost of Equity] + DIVIDE([Debt], [Equity] + [Debt]) * [Cost of Debt] * (1 – [Tax Rate]) | SELECT (equity / (equity + debt)) * cost_of_equity + (debt / (equity + debt)) * cost_of_debt * (1 – tax_rate) AS wacc FROM capital_structure WHERE company_id = :co AND period_end = :dt |
| Valuation | Market Multiples | Price-to-Earnings Ratio | Share price divided by EPS; how much investors pay per dollar of earnings. | P/E | stock valuation, market multiple, earnings multiple, equity valuation, investor sentiment | =SharePrice/EPS | P/E Ratio = DIVIDE([Share Price], [EPS]) | SELECT share_price / NULLIF(eps, 0) AS pe_ratio FROM market_data m JOIN financials f ON m.company_id = f.company_id WHERE f.fiscal_year = :yr |
| Valuation | Market Multiples | EV/EBITDA | Enterprise value divided by EBITDA; popular multiple for comparing companies across capital structures. | EV/EBITDA | enterprise value, valuation multiple, M&A, comparable analysis, capital structure neutral | =EnterpriseValue/EBITDA | EV/EBITDA = DIVIDE([Enterprise Value], [EBITDA]) | SELECT enterprise_value / NULLIF(ebitda, 0) AS ev_ebitda FROM valuation_summary WHERE fiscal_year = :yr |
| Valuation | Market Multiples | Price-to-Book Ratio | Share price divided by book value per share; compares market value to net assets. | P/B | book value, net assets, equity multiple, tangible assets, market-to-book | =SharePrice/(TotalEquity/SharesOutstanding) | P/B Ratio = DIVIDE([Market Cap], [Total Equity]) | SELECT share_price / NULLIF(equity / shares_outstanding, 0) AS pb_ratio FROM market_data m JOIN balance_sheet_summary b ON m.company_id = b.company_id WHERE b.period_end = :dt |
| Valuation | Market Multiples | Comparable Company Analysis | Valuing a company by benchmarking its multiples against similar publicly traded peers. | Comps | peer comparison, trading comps, benchmarking, market multiples, public comps | =AVERAGEIF(PeerGroup, Criteria, MultipleRange) | Peer Avg EV/EBITDA = CALCULATE(AVERAGE(Comps[EV_EBITDA]), Comps[Peer]=”Yes”) | SELECT AVG(ev_ebitda) AS peer_avg_ev_ebitda FROM valuation_summary WHERE peer_group = :grp AND fiscal_year = :yr |
| Valuation | Market Multiples | Precedent Transactions | Valuing a company based on prices paid in similar historical M&A deals. | M&A, acquisition premium, deal multiples, transaction comps, control premium | =AVERAGEIF(TransactionRange, SectorCriteria, MultipleRange) | Avg Transaction Multiple = CALCULATE(AVERAGE(MATransactions[Multiple]), MATransactions[Sector] = SELECTEDVALUE(DimSector[Sector])) | SELECT AVG(transaction_multiple) AS avg_multiple FROM ma_transactions WHERE sector = :sec AND txn_date >= ADD_MONTHS(SYSDATE, -60) | |
| Valuation | Enterprise Value | Enterprise Value | Market cap plus net debt; total value of a business to all capital providers. | EV | total firm value, net debt, market cap, debt, cash, acquisition price, EV bridge | =MarketCap+TotalDebt-CashAndEquivalents | Enterprise Value = [Market Cap] + [Total Debt] – [Cash] | SELECT market_cap + total_debt – cash AS enterprise_value FROM valuation_summary WHERE company_id = :co AND period_end = :dt |
| Valuation | Enterprise Value | Market Capitalization | Share price multiplied by total shares outstanding; equity market value. | Mkt Cap | equity value, market value, stock price, shares outstanding, float | =SharePrice*SharesOutstanding | Market Cap = [Share Price] * [Shares Outstanding] | SELECT share_price * shares_outstanding AS market_cap FROM market_data WHERE company_id = :co AND price_date = :dt |
| Budgeting & Forecasting | Planning | FP&A | Financial Planning & Analysis – forward-looking function covering budgets, forecasts, and scenario modeling. | FP&A | financial planning, budgeting, forecasting, strategic finance, business partnering | — N/A (process concept) | — N/A (process concept) | — N/A (process concept) |
| Budgeting & Forecasting | Planning | Rolling Forecast | A continuously updated projection that extends forward on a fixed horizon (e.g., always 12 months out). | continuous forecast, 12-month horizon, dynamic planning, reforecast, forward-looking | =OFFSET(ActualsStart, 0, CurrentMonth-1, 1, 12) | Rolling 12M Revenue = CALCULATE([Revenue], DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -12, MONTH)) | SELECT SUM(amount) AS rolling_12m_revenue FROM financials WHERE period_date BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE AND metric = ‘revenue’ | |
| Budgeting & Forecasting | Planning | Zero-Based Budgeting | Building a budget from scratch each period, requiring justification for every line item. | ZBB | cost reduction, justify spend, bottom-up budgeting, efficiency, cost discipline | — N/A (process concept) | — N/A (process concept) | — N/A (process concept) |
| Budgeting & Forecasting | Analysis | Variance Analysis | Comparing actual results to budget or prior period and explaining the drivers of the gap. | budget vs actual, BvA, favorable, unfavorable, explanation, gap analysis, drivers | =Actual-Budget | Variance = [Actual Revenue] – [Budget Revenue] Variance % = DIVIDE([Actual Revenue] – [Budget Revenue], ABS([Budget Revenue])) | SELECT actual_amt – budget_amt AS variance, (actual_amt – budget_amt) / NULLIF(ABS(budget_amt), 0) * 100 AS variance_pct FROM budget_actuals WHERE fiscal_year = :yr AND department_id = :dept | |
| Budgeting & Forecasting | Analysis | Sensitivity Analysis | Testing how changes in key assumptions (e.g., growth rate, discount rate) affect the output. | what-if, assumptions, data table, stress test, key drivers, model risk | =DataTable(InputCell1, InputCell2) | Sensitivity = CALCULATE([NPV], USERELATIONSHIP(Scenarios[Rate], Assumptions[Rate])) | SELECT scenario_name, SUM(fcf / POWER(1 + discount_rate, period)) AS npv FROM scenario_cashflows WHERE company_id = :co GROUP BY scenario_name, discount_rate | |
| Budgeting & Forecasting | Analysis | Scenario Analysis | Modeling outcomes under different sets of assumptions (base, upside, downside). | base case, upside, downside, bear, bull, scenario planning, contingency | =IF(ScenarioCell=”Upside”,UpsideFormula,IF(ScenarioCell=”Downside”,DownsideFormula,BaseFormula)) | Scenario Revenue = SWITCH(SELECTEDVALUE(Scenarios[Name]), “Upside”, [Upside Rev], “Downside”, [Downside Rev], [Base Rev]) | SELECT scenario, SUM(projected_revenue) AS revenue FROM scenario_forecast WHERE fiscal_year = :yr GROUP BY scenario | |
| Budgeting & Forecasting | Analysis | Break-Even Analysis | Identifying the sales volume at which total revenue equals total costs; zero profit point. | fixed costs, variable costs, contribution margin, breakeven point, unit economics | =FixedCosts/(PricePerUnit-VariableCostPerUnit) | Break-Even Units = DIVIDE([Fixed Costs], [Price per Unit] – [Variable Cost per Unit]) | SELECT fixed_costs / NULLIF(price_per_unit – variable_cost_per_unit, 0) AS breakeven_units FROM product_economics WHERE product_id = :prod | |
| Reporting & Visualization | Period Comparisons | Year-over-Year | Comparing a metric to the same period in the prior year. | YoY | annual growth, prior year, year comparison, growth rate, trend | =(CurrentYear-PriorYear)/ABS(PriorYear) | YoY Growth = DIVIDE([Revenue] – CALCULATE([Revenue], SAMEPERIODLASTYEAR(DimDate[Date])), ABS(CALCULATE([Revenue], SAMEPERIODLASTYEAR(DimDate[Date])))) | SELECT curr.revenue, prior.revenue AS prior_year_revenue, (curr.revenue – prior.revenue) / NULLIF(ABS(prior.revenue), 0) * 100 AS yoy_pct FROM financials curr JOIN financials prior ON curr.company_id = prior.company_id AND curr.fiscal_year = prior.fiscal_year + 1 |
| Reporting & Visualization | Period Comparisons | Quarter-over-Quarter | Comparing a metric to the immediately preceding quarter. | QoQ | quarterly growth, sequential growth, prior quarter, Q1 Q2 Q3 Q4, trend | =(CurrentQtr-PriorQtr)/ABS(PriorQtr) | QoQ Growth = DIVIDE([Revenue] – CALCULATE([Revenue], PREVIOUSQUARTER(DimDate[Date])), ABS(CALCULATE([Revenue], PREVIOUSQUARTER(DimDate[Date])))) | SELECT curr.revenue, prev.revenue AS prev_qtr_revenue, (curr.revenue – prev.revenue) / NULLIF(ABS(prev.revenue), 0) * 100 AS qoq_pct FROM quarterly_financials curr JOIN quarterly_financials prev ON curr.company_id = prev.company_id AND curr.fiscal_year = prev.fiscal_year AND curr.quarter = prev.quarter + 1 |
| Reporting & Visualization | Period Comparisons | Month-over-Month | Comparing a metric to the immediately preceding month. | MoM | monthly growth, sequential, prior month, trend, short-term change | =(CurrentMonth-PriorMonth)/ABS(PriorMonth) | MoM Growth = DIVIDE([Revenue] – CALCULATE([Revenue], PREVIOUSMONTH(DimDate[Date])), ABS(CALCULATE([Revenue], PREVIOUSMONTH(DimDate[Date])))) | SELECT curr.revenue, prev.revenue AS prev_month_revenue, (curr.revenue – prev.revenue) / NULLIF(ABS(prev.revenue), 0) * 100 AS mom_pct FROM monthly_financials curr JOIN monthly_financials prev ON curr.company_id = prev.company_id AND ADD_MONTHS(curr.period_month, -1) = prev.period_month |
| Reporting & Visualization | Period Comparisons | Year-to-Date | Cumulative performance from the start of the fiscal year to the current date. | YTD | cumulative, fiscal year, running total, partial year, performance to date | =SUMIF(YearCol, CurrentYear, AmountRange) | YTD Revenue = CALCULATE([Revenue], DATESYTD(DimDate[Date])) | SELECT SUM(amount) AS ytd_revenue FROM financials WHERE metric = ‘revenue’ AND fiscal_year = EXTRACT(YEAR FROM SYSDATE) AND period_date <= SYSDATE |
| Reporting & Visualization | Chart Types | Waterfall Chart | Shows how sequential positive/negative values lead to a final total; common in bridge analysis. | bridge chart, contribution, variance waterfall, changes, positive negative, Power BI | — Built via stacked bar with invisible base series in Excel | — Rendered in Power BI native Waterfall Chart visual | SELECT category, amount, SUM(amount) OVER (ORDER BY sort_order ROWS UNBOUNDED PRECEDING) AS running_total FROM waterfall_data WHERE report_id = :rpt ORDER BY sort_order | |
| Reporting & Visualization | Chart Types | Bridge Analysis | Visual decomposition of the change between two values, showing each contributing factor. | variance decomposition, driver analysis, waterfall, contributing factors, gap explanation | — Waterfall chart with labeled driver segments | — Use Power BI Waterfall or decomposition tree visual | SELECT driver, impact_amount, SUM(impact_amount) OVER (ORDER BY sort_order) AS cumulative FROM bridge_analysis WHERE from_period = :p1 AND to_period = :p2 ORDER BY sort_order | |
| Reporting & Visualization | KPIs | KPI | Key Performance Indicator – a quantifiable metric used to evaluate success against objectives. | KPI | performance metric, dashboard, target, scorecard, measurement, tracking | — N/A (framework concept) | KPI Status = IF([Actual] >= [Target], “On Track”, “At Risk”) | SELECT kpi_name, actual_value, target_value, CASE WHEN actual_value >= target_value THEN ‘On Track’ ELSE ‘At Risk’ END AS status FROM kpi_results WHERE period_date = :dt |
| Reporting & Visualization | KPIs | OKR | Objectives and Key Results – goal-setting framework linking qualitative goals to measurable outcomes. | OKR | goal setting, objectives, key results, strategic alignment, accountability, quarterly goals | — N/A (framework concept) | KR Progress % = DIVIDE([Actual], [Target]) | SELECT objective, key_result, actual_value, target_value, ROUND(actual_value / NULLIF(target_value, 0) * 100, 1) AS progress_pct FROM okr_tracking WHERE fiscal_quarter = :qtr |