Executive Dashboards That Disagree With Finance: Finding and Fixing the Gap

When an executive dashboard built on Dynamics 365 disagrees with the finance report, start with the reconciliation method, not the technology. Pick one period and one metric, then walk the number from source record to report cell, recording every filter, currency conversion, date basis and exclusion applied on the way. The gap is almost always one of five things: cancelled or reopened records, currency converted at transaction date versus report date, fiscal versus calendar periods, deleted or inactive rows still in the model, or a many-to-many join double counting. Record each difference in a variance worksheet, then make the structural fix that stops it recurring, and be plain about when a flat mega-table has to be remodelled.

Why should reconciliation start with one period and one metric?

Because two reports that disagree on everything cannot be debugged, and two reports that disagree on one number for one month can. Choose a closed period finance has already signed off, so their figure is fixed, and choose the metric executives argue about most, such as won revenue, invoiced revenue or open pipeline.

Then get both definitions in writing. Finance may mean invoiced revenue net of credit notes, recognised by invoice date in the reporting currency. The dashboard may mean the estimated or actual revenue on won opportunities by close date. Many gaps turn out to be two correct numbers answering different questions, and that is still a finding: the dashboard label has to change, or the measure does.

How do you walk a number from source record to report cell?

Export the rows behind each figure at the lowest grain available: one row per opportunity, order, invoice or invoice line, with its identifier. Then follow the dashboard figure through every layer it passes, and write down what each layer does.

1. Source: which Dataverse table and which column holds the amount, and whether it is the transaction currency or base currency column.
2. Extraction: the view, FetchXML, dataflow, Power Query steps or analytical copy that loads the rows, and any filter applied there.
3. Model: relationships, their direction and cardinality, and any calculated columns.
4. Measure: the exact DAX, including every filter it applies or removes.
5. Visual: page, visual and slicer filters, including ones hidden from the user.

Match the two row sets on a shared key such as the invoice number. Rows present on one side only, and rows present on both sides with different amounts or dates, are the whole investigation.

What are the five usual causes of the gap?

These account for most of the differences we find, and each leaves a recognisable trace in the matched rows.

• Cancelled or reopened records. An order cancelled after month end, an opportunity reopened and won again, or a revised quote that exists as several versions can each be counted twice or counted when finance excluded it. Check status and status reason filters on both sides.
• Currency date. Dataverse stores a base currency amount calculated with the exchange rate held on the record when the amount was written. Finance may convert at the invoice date rate or a period-end rate, and a Power BI model may convert at today's rate. Three conversions of the same amount will never agree.
• Fiscal versus calendar period. Finance reports by fiscal period while the model groups by calendar month, often through automatic date tables. Dates stored in UTC can also move a record written late on the last evening of the month into the next period.
• Deleted or inactive rows still in the model. Deactivated records remain in Dataverse, and an analytical export may keep rows flagged as deleted rather than removing them. If the model does not filter them, they count.
• Many-to-many join double counting. A revenue amount related to several products, territories or team members through a bridge table is summed once per related row, so a single opportunity contributes its value two or three times.

What goes in a variance worksheet?

One row per difference, kept until the two figures agree or the remaining gap is explained and accepted. The columns that make it usable are these.

• The period, the metric and both headline figures.
• Record identifier, amount in the dashboard and amount in the finance report.
• Cause category, from the five above or other.
• The layer where the difference arises: source, extraction, model, measure or visual.
• Evidence, such as the status history, rate used or duplicate relationship rows.
• Decision: fix the dashboard, fix the data, change the definition, or accept as a known difference.
• Owner and date closed.

When the worksheet explains the full gap for one month, repeat the walk for a second month before changing anything. A cause that explains one period but not the next usually has a partner you have not found yet.

How do you fix it at the model so it stops recurring?

Patching each visual with a filter moves the problem to the next report. Fix it once, where every report inherits it.

• A date table with the fiscal calendar. One marked date table carrying fiscal year, quarter and period columns agreed with finance, with automatic date tables switched off and each fact related on the date basis finance uses.
• One measure definition. Revenue is defined once in a shared semantic model, with the status exclusions and currency rule inside the measure, and every report uses that measure rather than its own copy.
• A star schema. Facts at one grain each, related to conformed dimensions for date, customer, product and currency, with many-to-many relationships resolved deliberately rather than left to bidirectional filters. Our Power BI for manufacturing guide walks through the same modelling decisions for plant reporting and is worth reading alongside this one.
• An agreed rate table. If finance converts at invoice date or period end, load those rates and convert in the model the same way, instead of relying on the base amount stored on the record.
• Deletion handling in the pipeline, so deleted and deactivated rows are excluded by rule rather than by memory.

If board totals are also hitting query limits, the move to an analytical copy is covered in our guide to Dynamics 365 reporting aggregate limits.

When does a flat mega-table have to be remodelled?

A single wide table joined from everything is quick to build and fine for one report. Remodel it when you see any of these signs.

• Header and line data share a row, so header amounts repeat and every measure needs a distinct-count workaround.
• Several date columns compete to be the reporting date and each report picks a different one.
• Every new question adds columns, and refresh time grows with them.
• Two reports built on it disagree with each other, not only with finance.

At that point more DAX will not fix it. The table has to be split into facts and dimensions. Sometimes the problem sits one layer lower, in a Dataverse model that stores the same amount in two tables or uses free text where a lookup belongs; our Dataverse data model remediation page covers correcting that in production. When finance figures live in a second system, consolidating reporting across Dynamics and a finance system sets out the options without a dedicated BI team.

Where does Solzet help?

Solzet works on the Dynamics 365 Customer Engagement, Dataverse and Power BI side of the reconciliation and integrates with your finance system rather than implementing it. If you are not sure whether the numbers or the build behind them can be trusted, a Dynamics 365 health check audits the data model, integrations and data integrity before any remodelling starts. Our senior consultants and full-stack developers bring 8+ years of Dynamics 365 and Power Platform work, delivered remotely from Yerevan, Armenia, directly or white-label for Microsoft partners.