Topic 25 - Build Company and Period Ratio Trends

Topic 25 — Build Company and Period Ratio Trends

Create Power BI DAX measures for quarterly trends, period-over-period variance, and company comparison.

SQL Server Views→Power BI Model→DAX Measures→Executive Dashboard

Production / Cybersecurity Warning

This exercise uses synthetic training data. Do not connect Power BI to production financial systems, corporate databases, or real accounting data without authorization, sandbox testing, least-privilege access, change-control approval, and cybersecurity compliance.

Objective

Create Power BI DAX measures for quarterly trends, period-over-period variance, and company comparison.

Approach A
Use ratios precalculated in Python and SQL Server.

Approach B
Recalculate selected ratios dynamically in Power BI using DAX.

Business Scenario

A ratio by itself is only a snapshot. Trend analysis shows whether the business is improving, weakening, stabilizing, or becoming riskier.

Output	Purpose
Period-over-period DAX measures	Topic output
Trend direction measures	Topic output
Company ranking measures	Topic output
Ratio trend visuals	Topic output

Step-by-Step Practice

Step 1 — Connect Power BI to SQL Server

Use Power BI Desktop to connect to Server: JCDCOMPUTER and Database: FinancialRatiosBI.

Power BI Connection

Get Data → SQL Server\nServer: JCDCOMPUTER\nDatabase: FinancialRatiosBI\nMode: Import

Step 2 — Import the Required Tables and Views

Use the SQL views for reporting and the dimension/fact tables when DAX calculations require base financial statement values.

Recommended imports

Dim_Company\nDim_Period\nFact_Income_Statement\nFact_Balance_Sheet\nFact_Cash_Flow\nFact_Debt_Ratios\nvw_All_Financial_Ratios\nvw_Executive_Financial_Summary\nvw_Latest_Period_Ratios

Step 3 — Create or Confirm Relationships

Connect the company and period dimensions to the imported fact tables and views.

Power BI Relationships

Dim_Company[CompanyID] → Fact and View tables[CompanyID]\nDim_Period[PeriodID] → Fact and View tables[PeriodID]

Step 4 — Add the DAX Measures

Create a new measure table named DAX Financial Measures and paste the measures below.

DAX - Final Measures

-- ============================================================
-- Financial Ratios Analysis in BI
-- Topic 25 - Build Company and Period Ratio Trends
-- ============================================================

-- ------------------------------------------------------------
-- Previous Period Measures
-- Requires Dim_Period[PeriodEndDate] to be a valid date column
-- ------------------------------------------------------------

Previous Period Current Ratio =
CALCULATE(
    [DAX Current Ratio],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

Previous Period Net Profit Margin =
CALCULATE(
    [DAX Net Profit Margin],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

Previous Period Debt Ratio =
CALCULATE(
    [DAX Debt Ratio],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

Previous Period OCF to Sales =
CALCULATE(
    [DAX Operating Cash Flow to Sales],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

-- ------------------------------------------------------------
-- Period-over-Period Variance
-- ------------------------------------------------------------

Current Ratio QoQ Change =
[DAX Current Ratio] - [Previous Period Current Ratio]

Net Profit Margin QoQ Change =
[DAX Net Profit Margin] - [Previous Period Net Profit Margin]

Debt Ratio QoQ Change =
[DAX Debt Ratio] - [Previous Period Debt Ratio]

OCF to Sales QoQ Change =
[DAX Operating Cash Flow to Sales] - [Previous Period OCF to Sales]

-- ------------------------------------------------------------
-- Trend Direction Labels
-- ------------------------------------------------------------

Current Ratio Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period Current Ratio]), "No Prior Period",
    [Current Ratio QoQ Change] > 0.01, "Improving",
    [Current Ratio QoQ Change] < -0.01, "Weakening",
    "Stable"
)

Net Profit Margin Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period Net Profit Margin]), "No Prior Period",
    [Net Profit Margin QoQ Change] > 0.01, "Improving",
    [Net Profit Margin QoQ Change] < -0.01, "Weakening",
    "Stable"
)

Debt Ratio Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period Debt Ratio]), "No Prior Period",
    [Debt Ratio QoQ Change] < -0.01, "Improving",
    [Debt Ratio QoQ Change] > 0.01, "Weakening",
    "Stable"
)

Cash Flow Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period OCF to Sales]), "No Prior Period",
    [OCF to Sales QoQ Change] > 0.01, "Improving",
    [OCF to Sales QoQ Change] < -0.01, "Weakening",
    "Stable"
)

-- ------------------------------------------------------------
-- Company Ranking
-- ------------------------------------------------------------

Company Rank by Net Profit Margin =
RANKX(
    ALLSELECTED(Dim_Company[CompanyName]),
    [DAX Net Profit Margin],
    ,
    DESC,
    Dense
)

Company Rank by Current Ratio =
RANKX(
    ALLSELECTED(Dim_Company[CompanyName]),
    [DAX Current Ratio],
    ,
    DESC,
    Dense
)

Company Rank by Debt Risk =
RANKX(
    ALLSELECTED(Dim_Company[CompanyName]),
    [DAX Debt Ratio],
    ,
    ASC,
    Dense
)

Step 5 — Validate the Result

Create cards, tables, and trend visuals to confirm that the measures respond to Company, Industry, Fiscal Year, and Quarter slicers.

Expected Output

Expected result

Trend measures created.
Quarter-over-quarter changes available.
Companies can be ranked by liquidity, profitability, and debt risk.

Business Interpretation

This topic moves the report from static KPIs to time-based analysis. The student can now explain movement, not only position.

Final Result of Topic 25

The Power BI model now supports a stronger BI architecture: stored ratios for auditability and dynamic DAX ratios for interactivity.

Next topic: Topic 26 — Executive Dashboard Using DAX Measures.

Topic 25 — Build Company and Period Ratio Trends

Crear medidas DAX en Power BI para tendencias trimestrales, variación período contra período y comparación de compañías.

SQL Server Views→Power BI Model→DAX Measures→Executive Dashboard

Advertencia de Producción / Ciberseguridad

Este ejercicio usa data sintética de entrenamiento. No conectes Power BI a sistemas financieros de producción, bases corporativas o data contable real sin autorización, pruebas en sandbox, acceso de mínimo privilegio, aprobación de control de cambios y cumplimiento de ciberseguridad.

Objetivo

Crear medidas DAX en Power BI para tendencias trimestrales, variación período contra período y comparación de compañías.

Approach A
Usar ratios precalculados en Python y SQL Server.

Approach B
Recalcular ratios seleccionados dinámicamente en Power BI usando DAX.

Escenario de Negocio

Un ratio por sí solo es una foto. El análisis de tendencias muestra si el negocio mejora, se debilita, se estabiliza o se vuelve más riesgoso.

Output	Purpose
Period-over-period DAX measures	Topic output
Trend direction measures	Topic output
Company ranking measures	Topic output
Ratio trend visuals	Topic output

Práctica Paso a Paso

Paso 1 — Conectar Power BI a SQL Server

Usa Power BI Desktop para conectarte a Server: JCDCOMPUTER y Database: FinancialRatiosBI.

Power BI Connection

Get Data → SQL Server\nServer: JCDCOMPUTER\nDatabase: FinancialRatiosBI\nMode: Import

Paso 2 — Importar Tablas y Views Requeridas

Usa las views SQL para reporting y las tablas de dimensión/fact cuando las medidas DAX necesiten valores base de estados financieros.

Recommended imports

Dim_Company\nDim_Period\nFact_Income_Statement\nFact_Balance_Sheet\nFact_Cash_Flow\nFact_Debt_Ratios\nvw_All_Financial_Ratios\nvw_Executive_Financial_Summary\nvw_Latest_Period_Ratios

Paso 3 — Crear o Confirmar Relaciones

Conecta las dimensiones de compañía y período con las tablas fact y views importadas.

Power BI Relationships

Dim_Company[CompanyID] → Fact and View tables[CompanyID]\nDim_Period[PeriodID] → Fact and View tables[PeriodID]

Paso 4 — Agregar las Medidas DAX

Crea una tabla de medidas llamada DAX Financial Measures y pega las medidas de abajo.

DAX - Final Measures

-- ============================================================
-- Financial Ratios Analysis in BI
-- Topic 25 - Build Company and Period Ratio Trends
-- ============================================================

-- ------------------------------------------------------------
-- Previous Period Measures
-- Requires Dim_Period[PeriodEndDate] to be a valid date column
-- ------------------------------------------------------------

Previous Period Current Ratio =
CALCULATE(
    [DAX Current Ratio],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

Previous Period Net Profit Margin =
CALCULATE(
    [DAX Net Profit Margin],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

Previous Period Debt Ratio =
CALCULATE(
    [DAX Debt Ratio],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

Previous Period OCF to Sales =
CALCULATE(
    [DAX Operating Cash Flow to Sales],
    DATEADD(Dim_Period[PeriodEndDate], -1, QUARTER)
)

-- ------------------------------------------------------------
-- Period-over-Period Variance
-- ------------------------------------------------------------

Current Ratio QoQ Change =
[DAX Current Ratio] - [Previous Period Current Ratio]

Net Profit Margin QoQ Change =
[DAX Net Profit Margin] - [Previous Period Net Profit Margin]

Debt Ratio QoQ Change =
[DAX Debt Ratio] - [Previous Period Debt Ratio]

OCF to Sales QoQ Change =
[DAX Operating Cash Flow to Sales] - [Previous Period OCF to Sales]

-- ------------------------------------------------------------
-- Trend Direction Labels
-- ------------------------------------------------------------

Current Ratio Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period Current Ratio]), "No Prior Period",
    [Current Ratio QoQ Change] > 0.01, "Improving",
    [Current Ratio QoQ Change] < -0.01, "Weakening",
    "Stable"
)

Net Profit Margin Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period Net Profit Margin]), "No Prior Period",
    [Net Profit Margin QoQ Change] > 0.01, "Improving",
    [Net Profit Margin QoQ Change] < -0.01, "Weakening",
    "Stable"
)

Debt Ratio Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period Debt Ratio]), "No Prior Period",
    [Debt Ratio QoQ Change] < -0.01, "Improving",
    [Debt Ratio QoQ Change] > 0.01, "Weakening",
    "Stable"
)

Cash Flow Trend =
SWITCH(
    TRUE(),
    ISBLANK([Previous Period OCF to Sales]), "No Prior Period",
    [OCF to Sales QoQ Change] > 0.01, "Improving",
    [OCF to Sales QoQ Change] < -0.01, "Weakening",
    "Stable"
)

-- ------------------------------------------------------------
-- Company Ranking
-- ------------------------------------------------------------

Company Rank by Net Profit Margin =
RANKX(
    ALLSELECTED(Dim_Company[CompanyName]),
    [DAX Net Profit Margin],
    ,
    DESC,
    Dense
)

Company Rank by Current Ratio =
RANKX(
    ALLSELECTED(Dim_Company[CompanyName]),
    [DAX Current Ratio],
    ,
    DESC,
    Dense
)

Company Rank by Debt Risk =
RANKX(
    ALLSELECTED(Dim_Company[CompanyName]),
    [DAX Debt Ratio],
    ,
    ASC,
    Dense
)

Paso 5 — Validar el Resultado

Crea cards, tablas y visuales de tendencia para confirmar que las medidas respondan a slicers de Company, Industry, Fiscal Year y Quarter.

Resultado Esperado

Expected result

Trend measures created.
Quarter-over-quarter changes available.
Companies can be ranked by liquidity, profitability, and debt risk.

Interpretación de Negocio

Este tópico mueve el reporte de KPIs estáticos a análisis temporal. El estudiante puede explicar movimiento, no solo posición.

Resultado Final del Topic 25

El modelo Power BI ahora soporta una arquitectura BI más fuerte: ratios almacenados para auditabilidad y ratios dinámicos DAX para interactividad.

Próximo tópico: Topic 26 — Executive Dashboard Using DAX Measures.