The power of Stellarise Connector is not only limited to the whole account data transferring to Power BI, but it enriches the PowerBI Dataset with extra information, already digested by the Stellarise Connector and ready-to-use for the end user.
Date dimensions
Regardless the different Date formats used by humans, Xero and PowerBI itself, the End User is always able to filter the data by a standard Date format.
Moving forward, the End User is also able to filter the information by human-meaning Date concept, for example the Quarters, satisfying requests like: “filter all the invoices in Q1 2020”.
Pushing this concept to the edge, Date ranges like “Quarters” can be used on a Calendar basis: “Q1” is January to March for example, or even considering the current Xero Financial Year: Q1 is April – June or whenever your FY set in Xero starts.
Positive and Negative Amounts
when When grabbing data from Xero, the specified amount is always a positive amount, where the “money incoming or outgoing” meaning is given by the Ledger context itself.
Stellarise Connectors adds a new field, alongside the returned Xero amount, that translates the Amount context in to the simpler “plus”ses an “minus”es, where a + amount is money coming in the Company and a – amount is money going out of the Company.
Positive/Negative Sell and Buy
alongside Alongside the Xero raw amounts, Stellarise Connector adds additional amount field suffixed with _credit (for sales) and _debit (for purchases) to Power BI.
Using this convention is then possible to report on Sales and Purchases using the Money +-. More precisely:
...
Note that these kind of calculations are not affecting the real data or other field types. The End User can always decide to adopt or ignore this convention in its reports.
Forecast the future
wellWell, based on the current data! Xero users normally end up creating Repeating Invoices In Xero, to simplify the Invoicing job and automatically create the new invoice when that invoice date has come.
Stellarise Connector is capable to read all of the Repeating Invoices defined in Xero and to report them into PowerBI, creating –into Power BI only – the future invoices that Xero would produce in the following years to come.
The End User can now exploit the Power BI data to have a clearer idea of what the Company is meant to do in the future.
Base Currency calculation
all All of the financial information in Xero is shipped with an ISO base currency (GBP, USD, AUD…) and a currency rate (0.123456, 1.234567…), whatever the currency in the Xero transaction is; the amount translation in the base currency is demanded to the End User.
Stellarise Connector enriches the data with the Base Currency amount already worked out.
Report Currency calculation
this This is a slightly different concept from the Base Currency, it is outside the Xero world.
Often, the End User owns a Xero Organisation which has a given Base Currency defined in the Xero settings. It is very difficult then to create Xero financial report in currencies other than the Base Currency, especially for past transactions.
Reading at the Date of the transaction, Stellarise Connector exploits the power of the Forex Python library (https://pypi.org/project/forex-python/) to translate the original amount into the defined Report Currency.
Conversion Currency Rates are the standard mid-market rates.
Xero Balancing Organisation
A virtual/fake Organisation is created and used by Stellarise Connector to aggregate two actual trading companies.