Skip to content

Automatic translation — the Portuguese version is the reference. View in Portuguese

Updated on 2026-10-04

Tutorial: measures and time comparisons

Create the Receita (revenue) measure and derive YTD, change vs last year and share of total, using formulas written in Portuguese.

On this page (10)

This tutorial starts from a simple revenue measure and gets to the comparisons every executive dashboard needs: year to date (YTD), change vs last year and share of total. Formula functions have Portuguese names (e.g., SOMA = sum).

Before you start, have the star schema and the calendar table ready. The mental model behind the formulas is in Row and filter context.

Example workspace: Comércio Aurora Ltda (aurora).

Prerequisites#

Step 1 — the base measure#

Create the core measure Receita (revenue) once, to reuse it in every visual:

code
Receita = SOMA(valor)

Note

Declare the measure in the model (central), not typed loose in each widget. That way "revenue" is the same everywhere, and the impact report shows who uses it before you edit it.

Step 2 — year to date (YTD)#

code
Receita YTD = ACUM_ANO(MEDIDA('Receita'); calendario.data)

ACUM_ANO (year to date) rewrites the date context to "from January 1 to the end of the current period". In a visual by month, the running total grows within the year and resets at the turn of the year.

Step 3 — change vs last year#

code
Receita AA = MESMO_PERIODO_ANO_ANTERIOR(MEDIDA('Receita'); calendario.data)
Variação % = DIVIDIR(MEDIDA('Receita') - MEDIDA('Receita AA'); MEDIDA('Receita AA'))

DIVIDIR (divide) protects against division by zero (an EMPTY result instead of an error). When there is no previous period in the data, the change comes out empty — the product does not make up a comparison number.

Step 4 — share of total#

code
Participação % = DIVIDIR(MEDIDA('Receita'); CALCULAR(MEDIDA('Receita'); TUDO()))

TUDO() takes no argument — it removes all of the group's filtering (category and month) from the denominator, so each cell shows its slice of the visual's grand total.

Step 5 — build the visual#

  1. Create a matrix

    Rows axis: produtos.categoria. Columns axis: calendario.mes.

  2. Add the measures

    Put Receita, Receita YTD and Variação % as values.

  3. Format

    Apply currency format to Receita and percentage to Variação % (see conditional formatting to highlight drops).

  4. Check

    Add up a cell by hand and compare it with the total in the footer.

Example (synthetic)#

monthReceitaReceita YTD
2026-0110001000
2026-0215002500
2026-0312003700

Expected result#

  • YTD accumulates within the year and resets the following January.
  • Variação % is blank when there is no previous year — never a fabricated number.
  • Each category's Participação % adds up to 100% in the total.

Common mistakes#

  • Time comparison without a linked calendar: time functions require the calendario dimension related to the fact table.
  • Repeating the formula in every widget: centralize the measure; otherwise the copies drift apart silently.
  • Expecting the product filter in the denominator with TUDO: that's the requested behavior — TUDO removes the filter on purpose.

Related#


Last reviewed: 2026-09-08.

Related links