Maps and fiscal calendars
Addresses with latitude and longitude, reference geography, and a fiscal calendar in every lab database: map it, chart it by fiscal period.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Store and use latitude and longitude for addresses.
- Measure the distance between two points with the haversine formula.
- Chart results by fiscal period, using the fiscal calendar in each lab database.
- Map results in Excel and Power BI.
Seven of the sample databases share the same two building blocks: addresses with coordinates for mapping, and a fiscal calendar for charting by fiscal period. They behave the same way in cpafirm, nonprofit, pqlab (its tables are prefixed core_), smarthome, bigbear, retailwh and mfgorders, so a map or a fiscal-year chart you build for one works for the others.
Addresses and coordinates
Every table that holds an address has the same columns:
| Column | Meaning |
|---|---|
address_line1, city, state, postal_code |
The address as entered |
county, country |
Added from the city (county names, US) |
latitude, longitude |
WGS84 decimal degrees, six decimals |
geocode_quality |
How the point was found: rooftop, street, postal_centroid or city_centroid (bigbear adds gps for mobile-app reports, address_match, neighborhood_centroid and failed) |
full_address |
The address in one line, for geocoding tools and map labels |
Where the address is missing the coordinates are null. Where only the city is known you get the city centroid.
| Database | Tables with coordinates |
|---|---|
cpafirm |
clients, offices, client_visits (origin and destination of each trip) |
nonprofit |
constituents (about 18,500 donors, volunteers and prospects), funders, vendors, program_sites, mobile_market_stops, event venues |
pqlab |
core_customers, core_stores, core_suppliers (HQ), core_shipments (destination and distance from the warehouse), plus the lab_geo_* practice tables |
smarthome |
homes, points_of_interest, geofences, trips, trip_points, and an indoor floor plan in feet |
bigbear |
parcels, facilities, assets, streets (segment endpoints), service_requests, cad_calls, permits, inspections, code_cases, work_orders, capital_projects, special_events, businesses, vendors, property_owners |
retailwh |
dim_store, dim_customer (latitude and longitude), dim_region (a rough boundary polygon as GeoJSON) |
mfgorders |
dim_customer, dim_plant |
Postal codes in these databases are fictitious (a real city prefix with random digits). The point for a postal code is a synthetic centroid placed near the city, so use postal_centroid rows for rough maps only.
Reference geography
Each database has the same three reference tables (in pqlab they are core_geo_states, core_geo_places and core_geo_postal_codes):
| Table | Holds |
|---|---|
geo_states |
The 50 states and DC: name, FIPS code, Census region and division, centroid, capital |
geo_places |
Each city in use with county, centroid, population, time zone and metro area |
geo_postal_codes |
A centroid for every postal code in the data |
Join to them on state_code, on city and state, or on postal_code. Use geo_states for filled (shaded-area) maps and geo_places for bubble maps.
Distance on a map
The same great-circle (haversine) formula works on SQL Server and MariaDB. This finds the five clients farthest from the office that serves them in cpafirm:
SELECT c.client_name, c.city, o.office_name,
ROUND(3958.8 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(c.latitude - o.latitude) / 2), 2)
+ COS(RADIANS(o.latitude)) * COS(RADIANS(c.latitude)) * POWER(SIN(RADIANS(c.longitude - o.longitude) / 2), 2))), 1) AS miles_from_office
FROM clients c
JOIN offices o ON o.office_id = c.office_id
WHERE c.latitude IS NOT NULL
ORDER BY miles_from_office DESC
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
Out-of-state clients (Arizona, for example) are served remotely, which is why they top the list. To build a catchment for the nearest store, use lab_geo_distance_pairs in pqlab, which already holds the miles and a flag for each customer's nearest store.
Mapping in Excel and Power BI
- Set the data category. In Power BI select
latitudeandlongitudeand set their data category to Latitude and Longitude. For a state map usestatewith the category State or Province. - Bubble map. Put
longitudeandlatitudeon the map visual, and size the bubbles by a measure such as donors, billed amount or miles. - Filled map. Join to
geo_statesand shade by state; usecensus_regionfor a region rollup. - Routes.
client_visits,tripsandlab_geo_route_pointshave an origin and a destination, or ordered points, which a line layer or a Shape map can draw. - Big maps. For thousands of points, round the coordinates (for example to one decimal) and group, so the map draws bins rather than every row.
- Excel. Use 3D Maps (Insert, 3D Map) or a Filled Map chart; for a Geography data type, build it from
full_addressorcityandstate.
Fiscal calendars
Every database has a fiscal-calendar table (fiscal_calendar, or core_fiscal_calendar in pqlab) with one row per day, and its main fact tables carry three ready-made columns so you do not need a join to filter by fiscal period:
| Column | Meaning |
|---|---|
fiscal_year |
The fiscal year (the naming rule is below) |
fiscal_quarter |
1 to 4 |
fiscal_period |
1 to 12, with 1 the first month of the fiscal year |
The calendar tables add fiscal_year_label (such as FY2026), fiscal_period_name (such as FY2026-P03), the period and year start and end dates, fiscal_week, fiscal_day_of_year, and flags for the last day of a period and of a year. Each also has a few extras for its domain.
| Database | Fiscal year | Named by | Extras in the calendar |
|---|---|---|---|
cpafirm |
July to June | year it ends | tax_season (Busy, Extension, Off) and tax_deadline |
nonprofit |
July to June | year it ends | giving_season, holidays, Giving Tuesday |
pqlab |
February to January | year it starts | a true 4-5-4 retail calendar (retail_year, retail_period, retail_week_of_period) and shopping seasons |
smarthome |
October to September (water year) | year it ends | utility_season and the rate plan in force |
bigbear |
July to June | year it ends | budget_phase, property tax installment dates, tourism_season, snow season and holidays |
retailwh |
April to March | year it ends | dim_date with fiscal year, quarter, period and period name |
mfgorders |
November to October | year it ends | dim_date with fiscal year, quarter, period and period name |
"Named by the year it ends" means FY2026 is the year ending in 2026 (July 2025 to June 2026 for a July start). pqlab names its year by the year it starts, to match its older core_calendar table; its retail_* columns are the 4-5-4 weeks, which differ from the calendar-month fiscal periods by a few days at the edges.
Fiscal quarters of billing, with no join:
SELECT fiscal_year, fiscal_quarter, COUNT(*) AS invoices, SUM(total_amount) AS billed FROM invoices WHERE fiscal_year BETWEEN 2024 AND 2026 GROUP BY fiscal_year, fiscal_quarter ORDER BY fiscal_year, fiscal_quarter;
Using the extras with a join (revenue by tax season in cpafirm):
SELECT f.tax_season, COUNT(*) AS invoices, SUM(i.total_amount) AS billed FROM invoices i JOIN fiscal_calendar f ON f.calendar_date = i.issue_date WHERE i.fiscal_year = 2025 GROUP BY f.tax_season ORDER BY billed DESC;
Giving by season in nonprofit:
SELECT f.giving_season, COUNT(*) AS gifts, SUM(g.amount) AS raised FROM gifts g JOIN fiscal_calendar f ON f.calendar_date = g.gift_date WHERE g.fiscal_year = 2025 GROUP BY f.giving_season ORDER BY raised DESC;
The retail calendar in pqlab:
SELECT f.retail_period, COUNT(*) AS orders, SUM(o.total) AS sales FROM core_orders o JOIN core_fiscal_calendar f ON f.calendar_date = o.order_date WHERE f.retail_year = 2025 GROUP BY f.retail_period ORDER BY f.retail_period;
Fiscal time in Power BI and Excel
- Use the calendar as your date table. Load the fiscal calendar, mark
calendar_dateas the date table, relate it to the fact table's date, and putfiscal_year_labelandfiscal_period_nameon slicers and axes. - Sort correctly. Sort
fiscal_period_nameby itself (it sorts as text, FY2026-P01 to P12) or sort a month name byfiscal_period, so July comes first for a July year. - Year to date. Pass the year-end date to time intelligence, for example
TOTALYTD([Billed], 'fiscal_calendar'[calendar_date], "6/30")for a June year end, or"9/30"forsmarthome. See time intelligence. - Map by fiscal period. Put
fiscal_yearorfiscal_quarteron a slicer next to a map, and the bubbles show that period's activity. - Compare years.
fiscal_periodlines up the same month of different fiscal years on one axis.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.