Using the Reconciliation Tool

Owned by Apostolos Karakaxas
Last updated: Jun 02, 2023 by Oleg Melnykov (Deactivated)Version comment
5 min read

Power BI Reconciliation application can be used by indirect and direct Microsoft CSPs to cross-check the charges between the interworks.cloud platform and Microsoft. You should start by selecting your reconciliation period and the filters you wish for narrowing down the results.

For reconciling a calendar month you should have uploaded at least the Microsoft reconciliation files for the month you wish to reconcile and for the previous one. This is necessary because for the monthly subscriptions the file from the previous month include charges for the current also.

Sometimes, it may be necessary to upload the next month also if Microsoft had errors in their charges that fixed in the next month’s file.

Filters

Configure the available filters to narrow down the results and filter out irrelevant data.

The following filters are available:

  • Reconciliation Period: Select the date range using the slider(s). The reconciliation period cannot be more than six months, and the earliest start date is February 1st, 2023. Note that the Valid Recon Period displayed is in mm/dd/yyyy format.

  • Account ID: Use this filter if you wish to reconcile the subscriptions of a specific end customer. You should enter the ID of the BSS account record.

  • Billing Account ID: For indirect CSPs, this filter can be used for reconciling the subscriptions of a specific reseller. You should enter the ID of the BSS account record.

  • MS Subscription ID: You can use this filter to reconcile a specific Microsoft subscription for the defined reconciliation period.

  • Microsoft Product Types: Use this filter for reconciling only specific types of Microsoft services:

  • CSP Partner - By default will be the name of your organization. This field will have more options only if you have country tenants and you have asked the same Power BI user to be able to reconcile all your country tenants.

  • Results Filtering -

    • All - Show all subscriptions within the specified reconciliation period

    • Discrepancies - Only show subscriptions with discrepancies

    • Missing Data - Only show subscriptions where data is missing. These are subscriptions that have been billed either from our system or from Microsoft but not from both systems.

Results Set

The results are displayed in the Reconciliation table. The reconciliation data can be found in the “BSS Data” and “MS Data” tables: The “BSS Data” table includes the platform invoice items, and the “MS Data” table includes the items found in MS reconciliation files.

Reconciliation Table

It includes a line per Microsoft subscription billed at least from one system (ours or Microsoft’s) in the given reconciliation period. It contains the following columns:

  • Subscription ID: The Microsoft Subscription ID.

  • BSS Total Cost: The total cost of the BSS invoice items for the specified reconciliation period.

  • MS Total Cost: The total cost of the Microsoft items for the specified reconciliation period.

  • Cost Difference - It is the difference between the two systems (in absolute value) along with one of the following symbols:

    • Green Checkmark - No difference or difference less than 1.00€

    • Red X - A difference of over 1.00€

    • Yellow Exclamation Mark - Insufficient data

Hovering over an entry shows more details in an expanded tooltip.

   

BSS Data Table

This table contains the invoice items returned by the interworks.cloud platform API.

The table contains the following columns:

  • Invoice Code: The invoice reference code

  • Invoice Date: the date the invoice was issued

  • Billing Account ID: The Bill To account ID

  • BSS Subscription ID: The ID of the subscription in the BSS

  • Product: The product the subscription is for

  • Start Date/End Date: The start and end dates of the invoice item.

  • Qty - Number of licenses

  • Unit Cost: The cost per license

  • Total Cost: The cost for the entire quantity

  • Unit Price: The price without discount(s)

  • Disc. %: The percentage of the discount applied to the retail price

  • Final Amount - The total amount for the entire quantity, with the discount applied. This is the invoice item’s total.

The fields Unit Cost and Unit Price are redundant for Azure plan subscriptions, as the totals are required to perform the reconciliation.

MS Data Table

This table contains data included in Microsoft reconciliation files.

  • Type: Legacy or NCE

  • Product: The product the subscription is for

  • Start Date/End Date: The start/end dates of the subscription

  • Change Type: The type of change as found in Microsoft’s reconciliation file.

  • Charge Start Date/Charge End Date - The start and end dates that are charged

  • Qty: Number of licenses

  • Unit Price: The price for a single license

  • Sub Total: The total for the entire quantity. VAT is not included.

  • Total: The sub-total including VAT

The excel fields we are using from Microsoft’s reconciliation files are the following:

For NCE Subscriptions

For Legacy Subscriptions

SubscriptionId

SyndicationPartnerSubscriptionNumber

SubscriptionStartDate

SubscriptionStartDate

SubscriptionEndDate

SubscriptionEndDate

ChargeStartDate

ChargeStartDate

ChargeEndDate

ChargeEndDate

ChargeType

ChargeType

UnitPrice

UnitPrice

Quantity

Quantity

Subtotal

Subtotal

TaxTotal

Tax

Total

TotalForCustomer

Currency

Currency

SubscriptionDescription

OfferName

TermAndBillingCycle

BillingCycleType

 

Detailed Invoices Tables

Select a subscription from the Reconciliation Table, and then select See Detailed Invoices to show the detailed invoices table.

 

The Detailed Invoices Table is separated into BSS Data and MS Data.

BSS Data

The BSS Data contains the following columns in addition to those seen in the previous section:

  • Account ID - The account issuing the invoice

  • Invoice Type - Is either debit or credit

  • Invoice Stage - The status of the invoice

  • Invoice Due Date - The expiration date of the invoice

MS Data

The MS Data contains only the ID of the subscription, in addition to the columns seen in the previous section.