9 minute read

Project Overview

I built a Total Cost of Ownership analysis tool for Mov3ment's fleet transition projects, but this is the story of how the project evolved, the platform limitations I hit, and the hard lessons I learned about aligning expectations early. The tool went through multiple iterations from August 2024 to March 2025, and the final version looks very different from what we originally envisioned. This project taught me as much about project management as it did about Power BI development.

Technology: Microsoft Power BI, Excel (VBA), Miro (database design)

Timeline: August 2024 - March 2025 (8+ iterations)

Key Lesson: Platform constraints and clear expectations matter more than fancy features

The Challenge

Mov3ment needed a tool to analyze vehicle fleets and calculate Total Cost of Ownership. Straightforward enough, right? But the real challenge became managing what the team wanted versus what Power BI could actually deliver.

The initial vision was ambitious. The team wanted maximum flexibility to slice data every possible way, create custom reports on the fly, and adjust parameters for dozens of different scenarios. As I got deeper into development, I kept hitting Power BI's limitations. The platform is powerful for visualization and analysis, but it's not a flexible application development environment.

Looking back, the bigger challenge wasn't technical. It was about understanding constraints early, communicating them clearly, and aligning everyone's expectations before building too much.

Designing from Scratch

Before writing a single line of code or creating any Power BI visuals, I spent time in Miro designing the database structure. This turned out to be one of the best decisions for the project.

I created a visual map showing:

  • All the data tables and their relationships (fact tables, dimension tables, lookup tables)
  • How information flows between different parts of the analysis
  • Where calculations happen and what feeds into them
  • What data comes from the Excel database versus what's calculated in Power BI

Having this map made development so much easier. When I needed to add a new feature or change something, I could look at the diagram and immediately see what would be affected. It also helped me explain the structure to the team when they had questions about why certain things worked the way they did.

The database itself is entirely custom. There was no existing structure to follow, so I had to figure out what data points would actually be useful for analyzing fleets. This meant a lot of back and forth with the team, trying different structures, and refining based on what worked in practice.

Custom Navigation and User Experience

One of the first things I decided was that the tool shouldn't look like a typical Power BI dashboard. The team wasn't going to be Power BI experts, so having them navigate tabs and built-in Power BI controls felt wrong.

Instead, I built a custom menu system using buttons. When you open the tool, you see a clean interface with clear navigation options. Click a button, and it takes you to the analysis you need. No hunting through tabs, no confusion about what's available.

This meant:

  • Designing button layouts and navigation flows
  • Using Power BI bookmarks and page navigation actions
  • Creating a consistent visual design across all screens
  • Making sure the interface felt intuitive, not like a dashboard

The custom navigation made the tool feel more like an application than a report. It also meant I could control exactly what the user saw at each step, which helped reduce confusion.

Hitting Platform Limitations

Here's where things got challenging. Power BI is excellent for what it's designed to do, but the team kept asking for features that pushed beyond those boundaries.

Some examples:

  • Dynamic parameter inputs: Users wanted to adjust dozens of parameters on the fly. Power BI doesn't handle this elegantly. You can do it, but it gets messy fast.
  • Complex conditional logic: "If this vehicle type, then calculate this way, unless this other condition, then..." Power BI's DAX can handle it, but it's not designed for application-level business logic.
  • Data entry and updates: The team wanted to input new fleet data directly in the tool. Power BI is for visualization and analysis, not data entry.
  • Custom export formats: Getting data out in exactly the format needed for other tools required workarounds.

Each limitation meant either finding a workaround (which sometimes made things more complex), or explaining why that feature wouldn't work well in Power BI. Some features we implemented anyway because they were critical, even if the implementation felt clunky. Others we had to push back on.

This created some frustration internally. The team could see what they wanted in their minds, and it was hard to explain why a tool that looked so flexible had these constraints.

Excel as the Data Layer

One decision that worked really well was keeping the data in Excel. This might sound like a step backward (why not use a proper database?), but it solved a critical problem.

The team working on fleet projects includes people with varying technical skills. Some are Excel experts, others are less technical. Everyone needed to be able to update the dataset when they completed a project, without depending on me or requiring special training.

Excel meant:

  • Anyone on the team can open it and understand the structure
  • Data entry doesn't require technical knowledge
  • No database administration or special permissions needed
  • Easy to back up, share, and version control (just save a new copy)
  • Power BI connects to it seamlessly

I built VBA forms to make data entry more structured and reduce errors, but the underlying data is in familiar Excel tables. This accessibility has been crucial for keeping the database up to date.

The Evolution of Scope

Looking at the file versions in the project folder, you can see the journey:

  • August 2024: Basic concept
  • September 2024: First working version
  • December 2024: Major structural changes
  • January 2025: Feature additions
  • March 2025: Refinements and current version

The scope changed significantly between the first version and what exists now. We started thinking it would be a simple comparison tool. It became a comprehensive analysis platform with TCO calculations, consumption estimation, emissions analysis, and transition planning exports.

Some of that growth was good. Real-world use showed what features would actually add value. But some of it came from scope creep and unclear requirements at the start. If I could do it again, I'd spend more time upfront defining what version 1.0 should include and what could wait for later iterations.

What I Learned About Alignment

The biggest lessons from this project weren't technical. They were about communication and managing expectations.

What went wrong:

  • I didn't clearly communicate Power BI's limitations early enough
  • We didn't have concrete mockups of what the tool would look like before I started building
  • Feature requests kept coming during development without re-evaluating priorities
  • I said "yes" to too many things trying to deliver everything the team wanted

This created internal frustration. The team was disappointed when certain features weren't possible or didn't work as smoothly as expected. I felt caught between technical constraints and user needs.

What I learned:

  • Show, don't tell: Sketches and mockups communicate better than descriptions. Next time, I'd create visual mockups before any development.
  • Define constraints upfront: "Here's what this platform can and can't do" should be the first conversation, not a surprise later.
  • Set clear milestones: Version 1.0 delivers X. Version 2.0 adds Y. Don't try to build everything at once.
  • Push back constructively: "We can do that, but it'll add two weeks and might make other features more complex. Still worth it?" is better than just saying yes.
  • Document decisions: Write down what was agreed and why. Memory is unreliable when projects stretch over months.

Applying Lessons to the Truck Rating API

The experience with this project directly shaped how I approached the Truck Rating API later. That project went much more smoothly because I applied what I learned here.

For the API project:

  • I created detailed structure diagrams before writing code
  • We aligned on what the API would and wouldn't do in the first meeting
  • I showed examples of similar APIs so expectations were realistic
  • The scope was clearly defined with specific features for version 1.0
  • We documented the architecture and design decisions as we went

The result was a smoother development process, fewer surprises, and a final product that matched what everyone expected. The lessons learned from the Fleet TCO Tool's challenges made the API project successful.

Technical Details

Despite the challenges, the technical implementation has some interesting aspects:

Database Structure

The Excel database uses a relational structure:

  • Vehicle specifications table (fact table)
  • Project details table (context)
  • Consumption data table (time series)
  • Cost breakdown tables (TCO components)
  • Lookup tables (vehicle types, fuel types, operational categories)

These tables connect through relationships designed in Miro and implemented in Power BI's data model.

Power BI Implementation

  • Custom navigation: Bookmarks and button actions create the menu system
  • DAX measures: Complex calculations for TCO, emissions, consumption estimates
  • Parameters: Limited use of parameters for scenarios (learned to keep this simple)
  • Conditional formatting: Visual feedback based on calculation results
  • Export functionality: DAX Studio and custom scripts for data extraction

VBA Forms

The Excel side includes VBA-based data entry forms with:

  • Input validation
  • Dropdown lists from lookup tables
  • Automatic calculation of derived fields
  • Data quality checks

What Actually Works Well

Despite the challenges and constraints, the tool does work and provides real value:

  • The custom navigation makes it accessible to non-Power BI users
  • TCO calculations based on historical data improve accuracy of estimates
  • The Excel database is maintainable by the whole team
  • The Miro design documentation makes updates easier
  • Export functionality supports transition planning work
  • The tool grows more valuable as the database expands

It's in active use across Mov3ment's projects, and the team has adapted to both its capabilities and limitations.

What I'd Do Differently

If I started this project over:

  • Week 1: Create detailed mockups and database diagrams. Get agreement before coding anything.
  • Week 2: Share a document listing platform capabilities and constraints. Make sure everyone understands what's possible.
  • Week 3: Define version 1.0 scope precisely. Create a "later versions" list for everything else.
  • During development: Weekly check-ins with work-in-progress demos. Surface issues early.
  • After each iteration: Document what changed and why. Keep a decision log.

These aren't revolutionary ideas, but I learned them the hard way on this project.

The Value of Constraints

Here's something I didn't expect: some of the platform limitations actually made the tool better.

Because Power BI isn't infinitely flexible, I had to make deliberate choices about what features were truly important. If I'd built this as a custom application with no constraints, it probably would have become bloated with half-used features.

The constraints forced simplicity. The tool does specific things well rather than trying to do everything poorly. That's actually a feature, not a bug.

Practical Applications

If you're building internal tools, especially in platforms like Power BI:

  • Understand platform limits early: Know what your tool can and can't do before promising features
  • Design before building: Use diagramming tools (Miro, Figma, even PowerPoint) to map structure and flow
  • Create mockups: Show what it will look like before it exists
  • Define version 1.0: Be very clear about what's in scope now versus later
  • Align expectations constantly: Don't assume everyone understands constraints or progress
  • Document decisions: Future you will thank present you
  • Learn from each project: Apply lessons to the next one (like I did with the API)

The technical skills matter, but project management and communication might matter more for successful delivery.

Current Status

The tool is actively used and continues to evolve. The database grows with each project, making future analyses more accurate. The team has adapted to what works well and found workarounds for limitations.

More importantly, I learned lessons about building tools, managing expectations, and working within constraints that have already made my next projects better. That might be the most valuable output from this entire experience.