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#
- A model with
vendaslinked tocalendario. - Core measures enabled in the model (see Measures, formulas and KPIs).
Step 1 — the base measure#
Create the core measure Receita (revenue) once, to reuse it in every visual:
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)#
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#
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#
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#
Create a matrix
Rows axis:
produtos.categoria. Columns axis:calendario.mes.Add the measures
Put Receita, Receita YTD and Variação % as values.
Format
Apply currency format to Receita and percentage to Variação % (see conditional formatting to highlight drops).
Check
Add up a cell by hand and compare it with the total in the footer.
Example (synthetic)#
| month | Receita | Receita YTD |
|---|---|---|
| 2026-01 | 1000 | 1000 |
| 2026-02 | 1500 | 2500 |
| 2026-03 | 1200 | 3700 |
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
calendariodimension 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 —TUDOremoves the filter on purpose.
Related#
Last reviewed: 2026-09-08.