# Resume & Portfolio Document — Freight-Cost "Gap Calculation" Analytics Platform

## User-confirmed context — 2026-10-18

> Source: direct user answers in the portfolio review. These are user-confirmed statements, not independently rerun repository or production measurements.

- **Ownership:** independently built the entire report.
- **Operational adoption:** used by the same group of more than 9 management controllers and managers (10+ people).
- **Scale:** approximately 16 million rows across the two snapshots, not 16 million per snapshot.
- **Purpose:** a separate report prepared each month to compare the current month against the prior month. ALL_Gap instead supports any selected month versus another month.
- **Business benefit:** enables variance analysis that was not practical manually at this scale; no previous manual-duration baseline or time-saving percentage was measured.

> Existing draft variants and unanswered placeholders are retained. This confirmed context governs personal ownership, adoption and outcomes; unresolved draft claims are not newly verified.


> Source of truth: `09_Gap_Calculation - September_Export_2026.pbip` (Power BI Project, PBIR report + TMDL semantic model).
> Everything below comes from the files in this repository. Placeholders in `[brackets]` are for real-world numbers that static code can't show.

> **Framing note for interviews:** this is a **BI / analytics engineering** project, not a web app. Use the mapping below when a recruiter asks about "frontend / backend / database":
>
> | Classic layer | What it is in this repo |
> |---|---|
> | Frontend / UI | Power BI report (PBIR JSON): 9 pages, 211 visual containers, custom navigation shell |
> | Backend / business logic | Tabular semantic model (TMDL): DAX measures, calculated tables, relationships |
> | Data ingestion / ETL | Power Query (M): parameterized Parquet folder ingestion and data cleansing |
> | Database / storage | Monthly Parquet snapshots on a network share + VertiPaq in-memory columnar engine (Import mode) |

---

## 1. High-Level Project Overview

### One-Line Pitch
A Power BI analytics product for Stellantis freight-cost controllers. It reconciles two monthly **AR extraction snapshots** of transport billing data and explains the **euro "gap"** between them, broken down by plant, cost center, GL account, steering partner, service level, scheduling pool and newly added loads.

### Problem & Solution
**Problem.** Each month, transport billing data (billing cases, loads, transport orders, cost types, amounts in EUR) is exported as a new Parquet snapshot. Finance and logistics controllers need to know **how much the total moved between two snapshots and why**: which plants, cost centers, carriers or loads caused the change, and which loads are new. Before this, answering that meant comparing two large exports by hand. The cost-center reference data (plant, legal entity, domain) also lived in a separate Excel file on the corporate document server.

**Solution / architecture.**
1. **Parameterized dual-snapshot ingestion (Power Query M):** four parameters (`Period1_Name/Source`, `Period2_Name/Source`) point to two monthly Parquet folders. A reusable custom function (`Transform File (2)` / `(3)`) parses every Parquet file in each folder, applies an 18-step cleansing pipeline, and tags each row with its snapshot name (`AR_Extraction_Name`).
2. **Unified fact table:** `Gap_Large` stacks both snapshots with `Table.Combine({AR_Actual, AR_Previous})`. Because both periods live in one long table with a discriminator column, any slicer or visual can compare them.
3. **Selection-driven gap engine (DAX):** `Total_Gap` reads which two snapshots the user has selected, orders them, and returns `latest − oldest`. A single measure then works on every page and every breakdown.
4. **Load-level diff table:** the calculated table `Comparison_Aug26_vs_Sep26` performs a `NATURALLEFTOUTERJOIN` between per-load aggregates of the new and old snapshots, which surfaces **newly added loads** and status changes.
5. **Enrichment:** cost centers are joined to an Excel master file loaded live from the intranet (`Web.Contents`). This lets users analyze by Plant, Domain and Legal Entity.
6. **Guided UX:** a landing "Filter Page" with 7 synced slicers, 8 analysis pages, an image-based sidebar navigation (72 page-navigation actions), and KPI cards that change color based on the measure value.

---

## 2. Comprehensive Tech Stack & Tooling

### Languages & Runtimes
| Technology | Evidence |
|---|---|
| **DAX** (Data Analysis Expressions) | 11 measures in `definition/tables/#Measures.tmdl`; calculated tables `DimDate`, `Comparison_Aug26_vs_Sep26`; ad-hoc queries in `DAXQueries/Query 1.dax`, `Query 2.dax` |
| **Power Query M** | 14 shared expressions in `definition/expressions.tmdl` plus partition queries for `Gap_Large` and `Cost Centers` |
| **TMDL** (Tabular Model Definition Language) | Every file under `SemanticModel/definition/` |
| **PBIR JSON** (Power BI Enhanced Report Format) | `Report/definition/**` (schemas: report 3.3.0, page 2.1.0, visualContainer 2.10.0) |
| **Analysis Services Tabular engine** | `database.tmdl` → `compatibilityLevel: 1600`; `defaultPowerBIDataSourceVersion: powerBI_V3` |

### Frontend / UI (Report Layer)
- **Power BI Report (PBIR)**, 9 pages, fixed 1280×720 canvas, `displayOption: FitToPage`.
- **Visuals used:** `slicer` (70), `image` (80, used for icons and navigation), `shape` (26), `cardVisual` (8, the new card visual), `clusteredBarChart` (4), `lineClusteredColumnComboChart` (3), `barChart` (2), `pivotTable` (2), `tableEx` (1), `azureMap` (1), `actionButton` (3). There are also 11 visual groups.
- **State management:** cross-page **slicer sync groups** on 63 slicers. Filter state carries across pages, similar to a global store.
- **Routing:** `PageNavigation` actions on images and buttons (72 bindings) give an app-style sidebar. `landingPageName` is set to the Filter Page.
- **Styling/theming:** base theme `CY24SU10`. Custom icon set of 10 registered PNG resources (Stellantis logo, globe, bank, calendar, scheduling-pool icons, etc.). `useStylableVisualContainerHeader` and `useEnhancedTooltips` are enabled.
- **Conditional formatting:** rule-based `Conditional` font colors on KPI cards (24 occurrences, e.g. red `#E74C3C` when `Gap % Change` passes a threshold). `FillRule` `linearGradient2` color scales on bar charts, the table and the map, driven by `Total_Gap` (8 occurrences).
- **Registered AppSource custom visuals:** Inforiver Charts and Zebra BI Tables appear in `report.json` → `publicCustomVisuals`. None of the current pages instantiates them.
- **Geospatial:** an Azure Maps visual plots `Total_Gap` by Plant.

### Backend / API (Semantic Model & Business Logic)
- **Tabular semantic model** in **Import** mode (all partitions use `mode: import`).
- **Interfaces:** the model is consumed through DAX queries (DAX Query View files in the repo) and the report. The XMLA endpoint is not configured in the repo.
- **Data access options:** `fastCombine` (privacy-level isolation turned off for performance), `legacyRedirects`, `returnErrorValuesAsNull`.
- **Locale handling:** `culture: en-US` for the model, `sourceQueryCulture: fr-FR` for parsing French source data.
- **Linguistic schema / Q&A:** `cultures/en-US.tmdl` (~295 KB of linguistic metadata, 112 term entries). This supports natural-language Q&A over the model.

### Database & Storage
| Component | Detail |
|---|---|
| **Apache Parquet** monthly snapshots | `Folder.Files("P:\...\monthly_export_data\only-parquet\2026\2026_08")` and `2026_09`, read with `Parquet.Document` |
| **Excel master data over HTTP** | `Excel.Workbook(Web.Contents("https://docinfogroupe.inetpsa.com/..."))` → sheet `Feuil1` → `Cost Centers` |
| **VertiPaq** columnar in-memory store | Import-mode partitions; local cache `.pbi/cache.abf` (git-ignored) |
| **Star-schema model** | Fact `Gap_Large` (38 columns); dimensions `DimDate` (24 columns) and `Cost Centers` (13 columns); derived fact `Comparison_Aug26_vs_Sep26` (10 columns) |

### DevOps, Testing & Tooling
- **Source control–friendly format:** PBIP + PBIR + TMDL (text-based, diffable). `.gitignore` excludes `**/.pbi/localSettings.json` and `**/.pbi/cache.abf` so binaries and user-specific state stay out of git.
- **Pro tooling flags** in `model.tmdl` → `PBI_ProTooling`: `DaxQueryView_Desktop`, `DaxCopilot_Change_Accepted_In_Desktop` (Copilot-assisted DAX), `MCP-PBIModeling` (model edited through the Model Context Protocol / AI-agent tooling), and `DevMode`.
- **AI-agent skills** in `.agents/skills/` (`powerbi-report-cli`, `semantic-model-authoring`): workflows for agent-assisted report and model authoring.
- **Query organization:** Power Query groups (`Parameters`, `Archive`, `Transform File from new period`, `Transform File from old period` and their helper subgroups).
- **Testing / CI:** **none in the repo.** There are no unit tests, DAX test harness or pipeline YAML. `DAXQueries/*.dax` act as manual validation scripts. (Good interview answer to "What would you add next?": see §6.)

---

## 3. Architecture & Technical Highlights

### 3.1 Parameterized, Reusable Snapshot-Ingestion Pipeline (Power Query M)
**Files:** `SemanticModel/definition/expressions.tmdl`, `tables/Gap_Large.tmdl`
- **Function-based folder combine.** `Transform File (2)` is a typed function `(Parameter2 as binary) => Parquet.Document(...)`. It is invoked once per file in the folder, hidden files are filtered out (`[Attributes]?[Hidden]? <> true`), and the schema is inferred from a sample binary (`Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))`). Any number of Parquet part-files per month works with no code change.
- **Moving to a new month means editing 4 parameters, not the code.** `Period1_Source`/`Period2_Source` (folder paths) and `Period1_Name`/`Period2_Name` (labels such as `AR_2026_08`, `AR_2026_09`) are `IsParameterQuery=true`. They can therefore be changed in the Service as dataset parameters.
- **Defensive data cleansing (18 transformation steps per snapshot):** explicit typing of 33 columns (`Table.TransformColumnTypes`), then `Text.Clean` + `Text.Trim` on every text key. `MaxCurrentStatus` is also lowercased. This makes join keys (LoadId, CostCenter, GL Account) deterministic across exports and prevents silent relationship misses from trailing spaces or control characters.
- **Lineage tagging:** `Table.AddColumn(..., "AR_Extraction_Name", each Period2_Name)` stamps every row with its snapshot. This discriminator column powers the whole comparison engine.
- **Composite key generation:** `"CA / Gl Account" = Text.Combine({[Billing Cases_CostCenter], [Billing Cases_Gl Account]}, " / ")` builds a cost-center/GL drill key at load time instead of at query time.

### 3.2 Selection-Aware Gap Engine (Generic DAX Measure)
**File:** `tables/#Measures.tmdl` → `Total_Gap`
```dax
VAR Selected_Extractions = VALUES('Gap_Large'[AR_Extraction_Name])
VAR Count_Selection     = COUNTROWS(Selected_Extractions)
RETURN IF(Count_Selection = 2,
    VAR First_Val  = TOPN(1, Selected_Extractions, 'Gap_Large'[AR_Extraction_Name], DESC)
    VAR Second_Val = TOPN(1, Selected_Extractions, 'Gap_Large'[AR_Extraction_Name], ASC)
    ... RETURN Half_1 + Half_2,   -- latest − oldest
    BLANK())
```
- **Design decision:** the snapshot names are not hardcoded. The measure reads the user's slicer selection (`VALUES`) and orders it with `TOPN` on the zero-padded `AR_YYYY_MM` naming convention, so lexical order equals chronological order.
- **Guard clause:** it returns `BLANK()` unless exactly 2 snapshots are selected. This stops misleading totals when 1 or 3+ snapshots are selected.
- **Reuse:** the same measure drives **every** breakdown (plant, cost center × GL account, steering partner, service level × cost type, scheduling pool, time series). It is used in KPI cards, bar charts, a matrix, a table and an Azure Map.
- **Derived KPIs** build on it: `Gap Direction` (Increase/Decrease/No Change), `Gap % Change` (`DIVIDE(Gap, ABS(Old_Amount), 0)` with safe division), and `Gap Contribution %` (`DIVIDE([Total_Gap], CALCULATE([Total_Gap], ALL(Gap_Large[Billing Cases_Gl Account])))`). The last one uses context removal to get each GL account's share of the total gap.

### 3.3 Load-Level Snapshot Diff via Lineage-Breaking Virtual Tables
**Files:** `tables/Comparison_Aug26_vs_Sep26.tmdl`, `DAXQueries/Query 2.dax`
- Builds two virtual tables with `CALCULATETABLE(SELECTCOLUMNS(SUMMARIZE(...)))`. Each one aggregates count of billing cases, `SUM(SumAmountInEuro)` and `MAX(MaxCurrentStatus)` **per LoadId, per snapshot**.
- **Key technical insight (it appears in the code comments):** `NATURALLEFTOUTERJOIN` needs join columns with matching names and no conflicting data lineage. Wrapping `SUMMARIZE` in `SELECTCOLUMNS` "creates an anonymous table, breaking lineage". After that, the two virtual tables can be joined on `Billing Cases_LoadId` without a model relationship.
- **Result:** a materialized diff table with `Old_*` and `Current_*` columns. Rows where the old side is blank are **newly added loads**. This powers the *Newly Added Loads* page (pivot `Snapshot_New × MaxCurrentStatus × Plant × LoadId`).
- Linked back to the fact table through a **bidirectional many-to-many relationship** on `Billing Cases_LoadId` (`crossFilteringBehavior: bothDirections`, `toCardinality: many`). Slicers on `Gap_Large` therefore also filter the diff table.
- `DAXQueries/Query 2.dax` keeps the earlier prototype (Dec-2025 vs Jan-2026) with the `-- FIX:` comment. This is evidence of iterative development done in DAX Query View.

### 3.4 Dynamic Star Schema With Data-Driven Calendar Dimension
**Files:** `tables/DimDate.tmdl`, `relationships.tmdl`, `tables/Cost Centers.tmdl`
- **Self-sizing calendar:** `DimDate` = `CALENDAR(MINX(ALL(Gap_Large), [ProcessingDate]), MAXX(ALL(Gap_Large), [ProcessingDate]))`. The date range grows with the data and does not need to be maintained.
- **24 derived attributes**, including correct **ISO-8601 week logic** (`WEEKNUM([Date] - WEEKDAY([Date],2) + 4, 2)` and the matching ISO year), Monday-start weeks, month boundaries (`EOMONTH`), and relative flags (`IsToday`, `IsCurrentMonth`, `IsCurrentYear`).
- **Sort-by-column** (`YearMonth` sorted by `YearMonthNo`) so text months sort chronologically in visuals.
- **5 relationships:** fact→`DimDate` (processing date), fact→`Cost Centers` (cost-center conformed dimension), fact↔comparison (bidirectional M:M), plus 2 auto date/time local tables (`joinOnDateBehavior: datePartOnly`) for the secondary date columns.
- **Master-data enrichment from the corporate document server:** `Cost Centers` loads an Excel file over HTTPS. It trims and cleans 13 columns, replaces error values with null (`Table.ReplaceErrorValues`), and filters out invalid keys (`null`, `"N/A"`, `"NA"`) so the dimension key stays unique.

### 3.5 App-Like Report UX: Synced Global Filters, Sidebar Routing, Semantic Coloring
**Files:** `Report/definition/pages/**/visual.json`, `pages.json`, `report.json`
- **Global filter state:** 63 of 70 slicers belong to **sync groups**. That covers 7 dimensions: snapshot, YearMonth, processing date, status, plant, domain and scheduling pool. A selection on the landing *Filter Page* follows the user through all 8 analysis pages. Each page also has a hierarchical **Plant → Cost center** slicer.
- **Navigation shell:** image icons wired to `PageNavigation` actions (72 bindings across pages), plus back buttons (`actionButton`, `shapeType: 'back'`). The pattern is a reusable component, copied consistently to every page and grouped with visual groups (11).
- **Semantic coloring:** KPI cards use `Conditional` → `Comparison` rules on `Gap % Change` (red `#E74C3C` for adverse movement). Charts, the table and the map use a `linearGradient2` `FillRule` on `Total_Gap`, so the biggest cost increases stand out right away.
- **Governed export:** `exportDataMode: AllowSummarized` blocks underlying-row export from visuals. This is a data-governance control for financial data.

### 3.6 Robustness & Data Quality Strategy
- **ETL-level:** `returnErrorValuesAsNull` at the model level, `Table.ReplaceErrorValues` on master data, explicit type enforcement, and text normalization on every join key.
- **Measure-level:** `DIVIDE(..., 0)` for division safety, `BLANK()` guard clauses, and `HASONEFILTER(DimDate[YearMonth])` with a `"-"` fallback in `Diff Billing Cases (Card - Text)` so a card never shows an ambiguous aggregate.
- **Repo hygiene:** caches and local settings are git-ignored, and the model is stored as text TMDL, which makes code review and diffs possible.

---

## 4. Quantifiable Metrics & Scope (From the Codebase)

| Metric | Value | Source |
|---|---|---|
| Report pages | **9** (1 landing filter hub + 8 analysis views) | `pages/pages.json` |
| Visual containers | **211** total (70 slicers, 80 images, 26 shapes, 20 data visuals, 3 buttons, 11 groups) | `pages/**/visual.json` |
| Data visual types | **8** (card, bar, clustered bar, combo line/column, matrix, table, Azure map, slicer) | visual.json `visualType` |
| Synced slicers / sync dimensions | **63** slicers across **7** dimensions | `"syncGroup"` |
| Navigation bindings | **72** `PageNavigation` actions | visual.json |
| Conditional-formatting rules | **24** rule-based + **8** gradient fill rules | `Conditional`, `FillRule` |
| Model tables | **8** (1 fact, 1 derived diff fact, 2 dimensions, 1 measure table, 3 auto date tables) | `definition/tables/` |
| Business tables (excl. auto-date) | **5** | — |
| Columns | Fact **38**, DimDate **24**, Cost Centers **13**, Comparison **10** (85 business columns) | `*.tmdl` |
| Relationships | **5** (incl. 1 bidirectional many-to-many) | `relationships.tmdl` |
| DAX measures | **11** | `#Measures.tmdl` |
| Calculated (DAX) tables | **2** (`DimDate`, `Comparison_Aug26_vs_Sep26`) | partitions `= calculated` |
| Power Query expressions | **14** (4 parameters, 2 custom functions, 2 snapshot pipelines, samples/helpers) | `expressions.tmdl` |
| Power Query query groups | **6** | `model.tmdl` |
| ETL steps per snapshot | **18** applied steps; **33** columns explicitly typed and cleaned | `AR_Actual`, `AR_Previous` |
| Data sources | **3** logical (2 Parquet folder snapshots + 1 Excel-over-HTTPS master) | M sources |
| Analysis dimensions surfaced | Plant, Cost Center, GL Account, Steering Partner, Service Level, Cost Type, Scheduling Pool, Status, YearMonth, Load | page visuals |
| Ad-hoc DAX validation queries | **2** | `DAXQueries/` |
| Linguistic (Q&A) term entries | **112** | `cultures/en-US.tmdl` |
| Automated tests / CI pipelines | **0** (not present) | — |
| Performance-related settings | Import/VertiPaq for all partitions; `fastCombine`; gap computed in one pass over a single stacked fact (no cross-table joins at query time); diff table pre-materialized at refresh | `model.tmdl`, partitions |

**Fill-in metrics to collect from real usage:** combined rows across both snapshots **approximately 16M** (per-snapshot split unmeasured), refresh duration `[X min]`, `.pbix` model size `[X MB]`, number of business users **10+**, hours saved per monthly close `[X h]`, size of gap explained `[€X M]`.

---

## 5. Ready-to-Use Resume Bullet Points (XYZ Format)

### Variation 1 — Full-Stack / General Software Engineering
1. **Independently built** a monthly freight-cost comparison report (Parquet ingestion → tabular semantic model → 9-page report) used by **10+ management controllers and managers**. It compares approximately **16M rows across current- and prior-month snapshots**, enabling variance analysis that was not practical manually. It is separate from ALL_Gap, which supports any selected month versus another month.
2. **Engineered** a generic, selection-aware DAX gap engine (`Total_Gap`) that serves **20+ visuals across 8 analytical views** from a single measure. It uses `VALUES`/`TOPN` to compute "latest − oldest" over any two user-selected monthly snapshots, so no per-month code changes are needed.
3. **Automated** monthly onboarding of new data snapshots. Moving to a new period now means editing **4 parameters** instead of rewriting queries, using reusable Power Query functions that ingest any number of Parquet part-files per folder. This reduced release effort by `[X%]`.
4. **Built** the project in the text-based PBIP/PBIR/TMDL format with git-ignored caches, which gave the BI asset diffable, reviewable source control. I also used AI-assisted tooling (DAX Copilot, MCP model editing) to speed up development by `[X%]`.

### Variation 2 — Backend / Data & Systems Architecture
5. **Designed** a star-schema tabular model (**8 tables, 85 business columns, 5 relationships**, including a bidirectional many-to-many bridge) that unifies two monthly snapshots containing **approximately 16M rows combined** into one discriminated fact table. Snapshot-over-snapshot variance is computed in a single VertiPaq scan, with response times under `[X ms]`.
6. **Engineered** a load-level snapshot diff as a calculated table. Wrapping `SUMMARIZE` in `SELECTCOLUMNS` breaks data lineage so `NATURALLEFTOUTERJOIN` can join the virtual tables without a relationship. It surfaced `[N]` newly added loads worth `[€X]` per cycle.
7. **Hardened** the data pipeline with an **18-step** cleansing stage (type enforcement, `Text.Clean`/`Text.Trim` on **33** key columns, error-to-null replacement, invalid-key filtering). This removed `[X%]` of join mismatches between transport billing data and the cost-center master data.
8. **Developed** a self-sizing DAX calendar dimension with **24 attributes**, including ISO-8601 week and year logic and relative-period flags. Its range comes from the fact table (`MINX`/`MAXX`), so date maintenance went to zero.

### Variation 3 — Frontend / Product Engineering
9. **Designed** an app-like reporting UX: **9 pages, 211 visual containers**, a landing filter hub, and a sidebar router (**72** page-navigation actions). Global filter state persists through **63 synced slicers** across **7 dimensions**, supporting **10+ management controllers and managers**.
10. **Implemented** semantic, data-driven visual encoding (**24** conditional color rules and **8** gradient fill scales on gap measures, plus an Azure Maps plant view). Controllers spot the top cost drivers right away, which cut investigation time by `[X%]`.
11. **Shipped** guarded KPI components (Gap €, Gap %, direction, billing-case counts) with `BLANK()`/`HASONEFILTER` guards and safe `DIVIDE` logic. This removed misleading totals on invalid filter combinations and lowered support questions by `[X%]`.
12. **Enforced** financial-data governance in the product layer (`exportDataMode: AllowSummarized`, curated drill paths Plant → Cost Center → GL Account). This met `[compliance/audit requirement]` while keeping self-service analysis.

---

## 6. Interview Prep: Deep-Dive Talking Points

### Talking Point 1 — Building a period-agnostic "gap" measure
**Files:** `SemanticModel/definition/tables/#Measures.tmdl` (`Total_Gap`, `Gap Direction`, `Gap Contribution %`), `DAXQueries/Query 1.dax`
- **S:** Every month brings a new AR extraction, and controllers compare different pairs (last month vs this month, or a quarter apart). Measures with hardcoded month names would need edits every cycle.
- **T:** Write one variance metric that works for any two snapshots, on any page, at any grain.
- **A:** Both snapshots go into one fact table with an `AR_Extraction_Name` discriminator. `Total_Gap` reads the current selection with `VALUES()`, requires exactly two items (`COUNTROWS = 2`, otherwise `BLANK()`), and orders them with `TOPN(1, …, DESC/ASC)`. This works because the naming convention `AR_YYYY_MM` sorts lexically in chronological order. The measure was prototyped in `Query 1.dax` (DAX Query View) and then promoted to the model.
- **R:** A single measure drives every breakdown: plant, cost center × GL, steering partner, service level, scheduling pool, time series and map. The derived measures (direction, contribution %) build on it.
- **Trade-offs to discuss honestly:**
  - `Gap % Change`, `Sum_Previous_Month` and `Sum_New_Month` still hardcode `"AR_2026_08"`/`"AR_2026_09"`, and `Diff Billing Cases` still references `AR_2025_10/11`. *Next step:* rewrite them with the same `VALUES`/`TOPN` pattern, or read the `Period*_Name` parameters through a disconnected table, so the whole model is period-agnostic.
  - Relying on lexical ordering is a contract with the naming convention. A safer design adds a numeric `SnapshotSortKey`.

### Talking Point 2 — Load-level diff without a model relationship (data lineage)
**Files:** `SemanticModel/definition/tables/Comparison_Aug26_vs_Sep26.tmdl`, `DAXQueries/Query 2.dax`, `relationships.tmdl`
- **S:** Totals alone don't explain a gap. Controllers needed to know **which loads are new, missing, or changed status** between snapshots.
- **T:** Produce a per-`LoadId` side-by-side of old vs new count, amount and status, and flag newly added loads.
- **A:** I built two `CALCULATETABLE(SUMMARIZE(...))` virtual tables, one filtered to each snapshot. The first `NATURALLEFTOUTERJOIN` attempt failed because both sides kept lineage to `Gap_Large[Billing Cases_LoadId]`. I fixed it by projecting through `SELECTCOLUMNS`, which creates anonymous columns and breaks lineage, so the join works by column name (see the `-- FIX:` comment in `Query 2.dax`). The result is a calculated table, linked back to the fact table through a bidirectional many-to-many relationship so all global slicers still apply.
- **R:** The *Newly Added Loads* page (pivot and combo chart on `Snapshot_New`, `Current_Amount`) shows new loads and their euro impact right away. The diff is computed once at refresh, not on every query.
- **Trade-offs:**
  - A left join shows new and changed loads but not *removed* loads. A full outer join (`UNION` of keys) would cover those.
  - The calculated table and its name are tied to Aug→Sep. Making it parameter-driven, or moving the diff upstream into Power Query, Dataflows or Fabric, would scale better.
  - Bidirectional M:M filtering is powerful but can create ambiguity or performance cost. I accepted it because the comparison table has a narrow grain (one row per load).

### Talking Point 3 — Repeatable, resilient monthly ingestion from Parquet plus intranet master data
**Files:** `SemanticModel/definition/expressions.tmdl` (`AR_Actual`, `AR_Previous`, `Transform File (2)/(3)`, `Period*` parameters), `tables/Gap_Large.tmdl`, `tables/Cost Centers.tmdl`
- **S:** Monthly exports arrive as multi-file Parquet folders on a network share, with inconsistent whitespace and control characters in key columns. Cost-center attributes (plant, domain, legal entity) live in an Excel file on the corporate document server and contain `N/A` keys and error cells.
- **T:** Make ingestion repeatable every month, keep join keys reliable, and enrich the fact table with organizational dimensions.
- **A:**
  - I used the folder-combine pattern: hidden-file filter, a typed custom function `(binary) => Parquet.Document(...)`, and schema inference from a sample file. The paths and snapshot labels are exposed as 4 required parameters.
  - Each snapshot goes through 18 steps: explicit types for 33 columns, `Text.Clean` + `Text.Trim` on every key, and lowercase status values. Each row is stamped with its snapshot label, and the two snapshots are stacked with `Table.Combine`.
  - For master data, `Web.Contents` → `Excel.Workbook`, followed by trim/clean, `Table.ReplaceErrorValues`, and filtering out `null`/`N/A`/`NA` keys to keep the dimension key unique.
  - At the model level I set `returnErrorValuesAsNull`, `fastCombine` and `sourceQueryCulture: fr-FR` so French-formatted sources parse correctly.
- **R:** Onboarding a new month is a parameter change and a refresh, with no code edits. Fact-to-cost-center joins are deterministic, which enables the Plant, Domain and Cost Center analyses plus the Azure Map. Result: `[X min]` refresh (unmeasured) for approximately **16M rows across the two snapshots**.
- **Trade-offs:**
  - `fastCombine` disables privacy-level isolation between sources. That is acceptable here because both sources are internal.
  - The `Sample File` helpers still point at the `2026_04`/`2026_05` folders. That is harmless (they are only used for schema inference) but a hidden dependency: if those folders are archived, refresh breaks. *Next step:* point the sample at `Period*_Source`.
  - `AR_Actual` and `AR_Previous` duplicate the same pipeline. Refactoring them into one function `fnLoadSnapshot(path, name)` would remove the duplication.
  - Using Import mode instead of DirectQuery was a deliberate choice: fast VertiPaq aggregation over snapshot data that doesn't change within the month.

### Bonus: "What would you improve next?" (shows senior judgment)
1. **Testing / CI:** add DAX unit tests (e.g. expected gap for fixture snapshots) run through DAX Query View or Tabular Editor scripts. Add Best Practice Analyzer rules and a deployment pipeline (Fabric Git integration or Azure DevOps) for the PBIP folder.
2. **Disable auto date/time** (`__PBI_TimeIntelligenceEnabled = 1` creates 3 hidden date tables). Use role-playing relationships to `DimDate` instead, to shrink the model.
3. **Make it fully period-agnostic:** remove the hardcoded snapshot literals and rename `Comparison_Aug26_vs_Sep26` to a generic name.
4. **Incremental refresh / history:** keep N snapshots in the fact table to support trend analysis beyond two periods. The `Gap evolution in year` page already points in that direction.
5. **Security:** add row-level security on `Cost Centers[Plant]`/`[Legal entity]` so each plant controller sees only their own perimeter.