In this post we are going to cover time intelligence and advanced DAX in Power BI: the date table, year to date, prior year and growth, calendar-based time intelligence, visual calculations, calculation groups and DAX user-defined functions. These are 7 real Power BI interview questions, and the last one is a scenario a manager loves to ask: finance runs a fiscal year that starts in July, and your numbers have to match theirs.
Time intelligence means comparing a number across time: year to date, same period last year, growth, rolling 12 months. Almost every finance or sales report needs it, so interviewers always ask. They also ask about the newer features, because they want to know if you keep up, and if you know which ones are still in preview.
Every example below runs on a small sample data set, so you can rebuild it in Power BI Desktop and check each number yourself.
Part 6 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: What do you need for time intelligence to work in Power BI?
- Question 2: How do you calculate year-to-date sales in DAX?
- Question 3: How do you compare with last year, and calculate year-over-year growth and percent?
- Question 4: What is calendar-based time intelligence, and why does it matter for fiscal, 4-4-5 or week-based calendars?
- Question 5: What are visual calculations, and when would you use them instead of a model measure?
- Question 6: What are calculation groups and DAX user-defined functions, and how do they reduce duplicated measures?
- Question 7: Scenario - finance's fiscal year starts in July, and they want YTD, prior-year YTD and a rolling 12 months that all match their numbers. How do you build it?
- The full SQL script
The short answer
Time intelligence needs a marked, contiguous date table. TOTALYTD and SAMEPERIODLASTYEAR give YTD and prior year, and a year end date like "6/30" handles fiscal years. Calculation groups and DAX UDFs remove copied logic; visual calculations are for one visual only.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales. Six are in the first quarter of 2025 and six in the first quarter of 2026, which is what lets us compare one year with the last.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
Question 1: What do you need for time intelligence to work in Power BI?
You need a proper date table: one row per day, no gaps, covering every date in your facts. Ours starts like this, one row for every day.
| date | year | quarter | month |
|---|---|---|---|
| 2025-01-01 | 2025 | Q1 | Jan |
| 2025-01-02 | 2025 | Q1 | Jan |
| 2025-01-03 | 2025 | Q1 | Jan |
Let's check that it really has no gaps.
| first_day | last_day | days | expected |
|---|---|---|---|
| 2025-01-01 | 2026-12-31 | 730 | 730 |
Check this result yourself with SQL
730 rows for 730 calendar days: the date table is contiguous, so it can be marked as a date table.
SELECT MIN(date) AS first_day, MAX(date) AS last_day,
COUNT(*) AS days,
MAX(date) - MIN(date) + 1 AS expected
FROM dim_date;It runs from 2025-01-01 to 2026-12-31. That is 730 days, and the table has 730 rows, so every day is there exactly once.
Then four things make it work:
- One row per day - no gaps, and it covers every date in the facts.
- Mark as date table - Power BI checks the date column is unique, has no blanks and is contiguous.
- A relationship - Date one-to-many to Sales, on the date.
- Auto date/time off - otherwise Power BI creates a hidden date table for every date column, and the model gets bigger for nothing.
If the date table has gaps, or stops before your last sale, classic time intelligence functions can give wrong or blank results.
Question 2: How do you calculate year-to-date sales in DAX?
The simplest way is TOTALYTD. You give it the measure and the date column, and it adds up everything from January 1 to the last date in the current filter context.
Under the hood it is CALCULATE with DATESYTD, so both versions below return the same thing. Know the CALCULATE form too, because it is easier to combine with other filters.
YTD Sales =
TOTALYTD ( [Total Sales], 'Date'[Date] )
// same result with CALCULATE
YTD Sales (2) =
CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) )Here is the first quarter of 2026 by month.
| month | sales | ytd_sales |
|---|---|---|
| Jan | 370.00 | 370.00 |
| Feb | 140.00 | 510.00 |
| Mar | 620.00 | 1130.00 |
Check this result yourself with SQL
Year to date adds every month from January 1 up to the current month: 370, 510, 1130.
WITH m AS (
SELECT d.month_no, d.month, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_date d ON d.date = f.order_date
WHERE d.year = 2026
GROUP BY d.month_no, d.month)
SELECT month, sales,
SUM(sales) OVER (ORDER BY month_no) AS ytd_sales
FROM m
ORDER BY month_no;January sold 370.00, so year to date is 370.00. February adds 140.00, for 510.00. March adds 620.00, for 1130.00.
Year to date resets every January 1, because the year end defaults to December 31. Keep that in mind, because it matters for the fiscal year scenario in Question 7.
Question 3: How do you compare with last year, and calculate year-over-year growth and percent?
Shift the dates back one year with SAMEPERIODLASTYEAR inside CALCULATE. Year over year is this year minus last year, and the percent is that difference divided by last year, using DIVIDE.
PY Sales =
CALCULATE ( [Total Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
// same as DATEADD ( 'Date'[Date], -1, YEAR )
YoY = [Total Sales] - [PY Sales]
YoY % = DIVIDE ( [YoY], [PY Sales] )| month | sales | py_sales | yoy | yoy_pct |
|---|---|---|---|---|
| Jan | 370.00 | 330.00 | 40.00 | 12.12 |
| Feb | 140.00 | 110.00 | 30.00 | 27.27 |
| Mar | 620.00 | 350.00 | 270.00 | 77.14 |
| Q1 | 1130.00 | 790.00 | 340.00 | 43.04 |
Check this result yourself with SQL
Sales minus prior-year sales, divided by prior-year sales. Q1 2026 is up 340, or 43.04 percent.
WITH m AS (
SELECT d.month_no, d.month,
SUM(CASE WHEN d.year = 2026 THEN f.qty * f.unit_price END) AS sales,
SUM(CASE WHEN d.year = 2025 THEN f.qty * f.unit_price END) AS py_sales
FROM fact_sales f JOIN dim_date d ON d.date = f.order_date
WHERE d.quarter = 'Q1'
GROUP BY d.month_no, d.month)
SELECT month, sales, py_sales, yoy, yoy_pct FROM (
SELECT month, month_no AS srt, sales, py_sales, sales - py_sales AS yoy,
CAST(100.0 * (sales - py_sales) / NULLIF(py_sales, 0) AS DECIMAL(10,2)) AS yoy_pct
FROM m
UNION ALL
SELECT 'Q1', 99, SUM(sales), SUM(py_sales), SUM(sales) - SUM(py_sales),
CAST(100.0 * (SUM(sales) - SUM(py_sales)) / NULLIF(SUM(py_sales), 0) AS DECIMAL(10,2))
FROM m) t
ORDER BY srt;January 2026 sold 370.00 against 330.00 a year earlier, up 40.00, or 12.12 percent. February is up 30.00, 27.27 percent. March is up 270.00, 77.14 percent. For the whole first quarter it is 1130.00 against 790.00: up 340.00, or 43.04 percent.
The functions you should be able to name:
- SAMEPERIODLASTYEAR - the same dates, one year back.
- DATEADD with -1, YEAR - the same result.
- DIVIDE - when there is no prior year, it returns blank instead of infinity. Use it, not the slash.
- PARALLELPERIOD - returns the whole shifted period, like the full previous year, not just the same selected dates.
Question 4: What is calendar-based time intelligence, and why does it matter for fiscal, 4-4-5 or week-based calendars?
Classic time intelligence assumes a normal Gregorian calendar. You can move the year end for a fiscal year, but it breaks down for retail 4-4-5 calendars and week-based years.
Calendar-based time intelligence lets you define calendars on your date table. You map columns to categories like year, quarter, month, week and date. Then you pass the calendar name to the function instead of the date column.
// classic: pass the date column
YTD = TOTALYTD ( [Sales], 'Date'[Date] )
// calendar-based (preview): pass a calendar
YTD = TOTALYTD ( [Sales], 'Fiscal Calendar' )It supports fiscal, 4-4-5, ISO and week-based calendars, and adds week to date with TOTALWTD. Marking the date table is not required, gaps are allowed, and Microsoft calls it the recommended approach.
| Classic | Calendar-based | |
|---|---|---|
| CALENDARS | Gregorian; fiscal via year end date | fiscal, 4-4-5, ISO, week-based |
| DATE TABLE | marked, contiguous | calendar categories; gaps allowed |
| WEEKS | no week-to-date function | TOTALWTD |
| STATUS | GA | Preview |
Say clearly that it is still in preview. You turn it on under preview features, and it is not supported with live connections or composite models. Flag that risk before you use it in production.
Question 5: What are visual calculations, and when would you use them instead of a model measure?
Visual calculations are DAX calculations defined on a visual. They have been generally available since May 2026. They work on the data that is already in the visual, so they can refer to the previous, next, first or last row.
Here are two of them: a running sum and a 3 month moving average.
// visual calculations, defined on the visual
Running Sales = RUNNINGSUM ( [Sales] )
Moving Avg = MOVINGAVERAGE ( [Sales], 3 )Let's see what RUNNINGSUM does on sales by month.
| month | sales | running_sum |
|---|---|---|
| 2025-01 | 330.00 | 330.00 |
| 2025-02 | 110.00 | 440.00 |
| 2025-03 | 350.00 | 790.00 |
| 2026-01 | 370.00 | 1160.00 |
| 2026-02 | 140.00 | 1300.00 |
| 2026-03 | 620.00 | 1920.00 |
Check this result yourself with SQL
RUNNINGSUM adds the rows of the visual in order and ends at the grand total of 1920.
WITH m AS (
SELECT d.year, d.month_no, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_date d ON d.date = f.order_date
GROUP BY d.year, d.month_no)
SELECT year || '-' || LPAD(month_no::text, 2, '0') AS month, sales,
SUM(sales) OVER (ORDER BY year, month_no) AS running_sum
FROM m
ORDER BY year, month_no;It starts at 330.00 in January 2025, adds each month in order, and ends at 1920.00, the grand total of all 12 sales. MOVINGAVERAGE works the same way, over a window of rows.
They are simple, and often faster, because they run on the aggregated data in the visual. They are great for running totals, moving averages and percent of parent.
But they live in that one visual. You can't reuse them in other visuals, they don't go into exports, and they are not in the model.
| Visual calculation | Measure | |
|---|---|---|
| LIVES IN | one visual | the model |
| REUSE | no | any visual or report |
| EXPORTS | no | yes |
| BEST FOR | running sum, moving avg | shared, governed KPIs |
So anything shared or governed, like a KPI finance relies on, should be a measure.
Question 6: What are calculation groups and DAX user-defined functions, and how do they reduce duplicated measures?
Say you have 10 measures, and each one needs year to date, prior year and year over year. Write them one by one, and that is 30 extra measures with the same logic copied.
A calculation group fixes that. Each item, like YTD or PY, wraps SELECTEDMEASURE in the time logic, and applies it to whatever measure is in the visual. Now it is 10 measures and 3 calculation items, not 30 copies. You can create calculation groups in Model view in Desktop.
// calculation group: Time Intelligence
// item: YTD
CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) )
// item: PY
CALCULATE ( SELECTEDMEASURE (),
SAMEPERIODLASTYEAR ( 'Date'[Date] ) )Things to mention: SELECTEDMEASURE means any measure in the visual, you should know about precedence and dynamic format strings, and implicit measures get discouraged once a model has calculation groups.
DAX user-defined functions have been generally available since June 2026. You define a function once, with typed parameters, and call it from any measure. Here is a SafePct function that wraps DIVIDE.
DEFINE
FUNCTION SafePct = ( n : NUMERIC, d : NUMERIC ) =>
DIVIDE ( n, d )
// use it in any measure
YoY % = SafePct ( [YoY], [PY Sales] )They need compatibility level 1702 or higher.
The one-line summary: a calculation group changes how any measure is calculated, and a function packages logic you reuse. Either way, there is one place to fix a bug, not thirty.
Question 7: Scenario - finance's fiscal year starts in July, and they want YTD, prior-year YTD and a rolling 12 months that all match their numbers. How do you build it?
This is the scenario managers love. Finance runs July to June, and your report has to agree with their numbers to the cent.
Here is the sequence to walk through:
- Start in the date table. Add fiscal columns like fiscal year, fiscal quarter and fiscal month number, so July is month one.
- Fiscal YTD. Pass the year end date,
"6/30", as the last argument of TOTALYTD, so the year resets on July 1. - Prior year fiscal YTD. Shift that measure with SAMEPERIODLASTYEAR.
- Rolling 12 months. Use DATESINPERIOD, going back 12 months from the last date in context.
FYTD Sales =
TOTALYTD ( [Total Sales], 'Date'[Date], "6/30" )
PY FYTD =
CALCULATE ( [FYTD Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Rolling 12M =
CALCULATE ( [Total Sales], DATESINPERIOD (
'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ) )| month | fytd | py_fytd | r12m |
|---|---|---|---|
| Jan 2026 | 370.00 | 330.00 | 830.00 |
| Feb 2026 | 510.00 | 440.00 | 860.00 |
| Mar 2026 | 1130.00 | 790.00 | 1130.00 |
Check this result yourself with SQL
Fiscal year 2026 runs July 2025 to June 2026. Rolling 12 months always looks back 12 months, so it differs from fiscal YTD.
WITH m AS (
SELECT d.year, d.month_no, d.month,
CASE WHEN d.month_no >= 7 THEN d.year + 1 ELSE d.year END AS fiscal_year,
COALESCE(SUM(f.qty * f.unit_price), 0) AS sales
FROM dim_date d LEFT JOIN fact_sales f ON f.order_date = d.date
GROUP BY d.year, d.month_no, d.month),
c AS (
SELECT m.*,
SUM(sales) OVER (PARTITION BY fiscal_year ORDER BY year, month_no) AS fytd,
SUM(sales) OVER (ORDER BY year, month_no ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS r12m
FROM m)
SELECT c.month || ' ' || c.year AS month, c.fytd, p.fytd AS py_fytd, c.r12m
FROM c LEFT JOIN c p ON p.year = c.year - 1 AND p.month_no = c.month_no
WHERE c.year = 2026 AND c.month_no <= 3
ORDER BY c.month_no;Our fiscal year 2026 started in July 2025, but the sample has no sales from July to December. So in January 2026, fiscal year to date is 370.00, the prior year is 330.00, and the rolling 12 months is 830.00, because it reaches back to February 2025. By March, fiscal year to date is 1130.00, against 790.00 last year.
Notice that rolling 12 months and fiscal YTD are different numbers in January and February. That is expected: rolling 12 always looks back 12 months, while YTD starts again at the beginning of the fiscal year.
Then finish the answer:
- Put this logic in a calculation group, so every measure gets it without copies.
- For 4-4-5 calendars, calendar-based time intelligence is the newer option, but it is in preview.
- Reconcile every number with finance before you ship.
Key points to remember
- A marked, contiguous date table; auto date/time off
- TOTALYTD for YTD, SAMEPERIODLASTYEAR for prior year
- Fiscal years: year end "6/30"; calendar-based TI is preview
- Visual calculations for one visual, measures for shared logic
- Calculation groups and UDFs stop measure explosion
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind every result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #6 - Time intelligence and advanced DAX
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q06; SET search_path TO pbi_q06;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Date table check: one row per day, no gaps
SELECT MIN(date) AS first_day, MAX(date) AS last_day,
COUNT(*) AS days,
MAX(date) - MIN(date) + 1 AS expected
FROM dim_date;
-- Step 2: Year to date by month, Q1 2026 (TOTALYTD)
WITH m AS (
SELECT d.month_no, d.month, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_date d ON d.date = f.order_date
WHERE d.year = 2026
GROUP BY d.month_no, d.month)
SELECT month, sales,
SUM(sales) OVER (ORDER BY month_no) AS ytd_sales
FROM m
ORDER BY month_no;
-- Step 3: Q1 2026 vs Q1 2025 by month: YoY and YoY percent
WITH m AS (
SELECT d.month_no, d.month,
SUM(CASE WHEN d.year = 2026 THEN f.qty * f.unit_price END) AS sales,
SUM(CASE WHEN d.year = 2025 THEN f.qty * f.unit_price END) AS py_sales
FROM fact_sales f JOIN dim_date d ON d.date = f.order_date
WHERE d.quarter = 'Q1'
GROUP BY d.month_no, d.month)
SELECT month, sales, py_sales, yoy, yoy_pct FROM (
SELECT month, month_no AS srt, sales, py_sales, sales - py_sales AS yoy,
CAST(100.0 * (sales - py_sales) / NULLIF(py_sales, 0) AS DECIMAL(10,2)) AS yoy_pct
FROM m
UNION ALL
SELECT 'Q1', 99, SUM(sales), SUM(py_sales), SUM(sales) - SUM(py_sales),
CAST(100.0 * (SUM(sales) - SUM(py_sales)) / NULLIF(SUM(py_sales), 0) AS DECIMAL(10,2))
FROM m) t
ORDER BY srt;
-- Step 4: Visual calculation RUNNINGSUM over the months
WITH m AS (
SELECT d.year, d.month_no, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_date d ON d.date = f.order_date
GROUP BY d.year, d.month_no)
SELECT year || '-' || LPAD(month_no::text, 2, '0') AS month, sales,
SUM(sales) OVER (ORDER BY year, month_no) AS running_sum
FROM m
ORDER BY year, month_no;
-- Step 5: Fiscal year starting July: FYTD, prior-year FYTD, rolling 12 months
WITH m AS (
SELECT d.year, d.month_no, d.month,
CASE WHEN d.month_no >= 7 THEN d.year + 1 ELSE d.year END AS fiscal_year,
COALESCE(SUM(f.qty * f.unit_price), 0) AS sales
FROM dim_date d LEFT JOIN fact_sales f ON f.order_date = d.date
GROUP BY d.year, d.month_no, d.month),
c AS (
SELECT m.*,
SUM(sales) OVER (PARTITION BY fiscal_year ORDER BY year, month_no) AS fytd,
SUM(sales) OVER (ORDER BY year, month_no ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS r12m
FROM m)
SELECT c.month || ' ' || c.year AS month, c.fytd, p.fytd AS py_fytd, c.r12m
FROM c LEFT JOIN c p ON p.year = c.year - 1 AND p.month_no = c.month_no
WHERE c.year = 2026 AND c.month_no <= 3
ORDER BY c.month_no;
Part 7: Performance Optimization
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.