Financial Dashboard
In this section, you’ll learn how to turn raw data into an interactive Power BI report. We’ll cover everything you need to know to create a complete project from start to finish.
The Financial Dashboard shows key information like income, expenses, profits, and cash flow, helping you understand your business’s financial health. It makes it easy to see trends, track performance, and make smart financial decisions. Try it yourself first! Build the dashboard and, if you get stuck, refer to the video for help.
1. Connect Power BI to your data source, download this Excel Financial Dashboard.
2. Understand the Tables of your data set, we have ten tables in the data set:
• Account: This table helps organise and keep track of different financial accounts. It shows details like the account type, name, and how to sort or group
accounts in a clear order for reporting and management. This is the account master table.
• Company: This table is the master company name. There are two companies QR Solution and Unique Solution.
• Customer: This table tracks detailed information about a customer, including their billing and shipping addresses, contact details, and payment status. It
helps organize customer data for billing, communication, and delivery.
• General Ledger: This table tracks detailed financial transaction data. It includes information about the type of transaction (invoice, payment), amounts,
dates, clients, vendors, and related accounts. It also includes tax and report-related data, such as the tax amount and the reporting basis (cash or
accrual).
• Invoice: This table tracks detailed information about transactions, including customer info, payment, products, and shipping details. It helps you manage
when payments are due, which items were sold, how much is left to pay, and more. It also keeps track of the shipping and billing details for the customer.
• Ap Aging: This table tracks accounts payable (AP) transactions, showing key details like the amount owed, transaction type, due date, and vendor
information. It also includes fields to sort and organize transactions by category, location, and company for easier tracking and reporting.
• Vendor: This table tracks vendor details like contact info (phone, address), business info (company name), and financial info (balance owed). It also
includes payment terms, billing address, and whether the vendor needs to be reported for taxes.
• Account Sort: This table organises accounts by sorting them (Acc Sort), identifying each account by name (Account). It helps keep the Income
statement sheet well-structured and easy to manage.
• Date: The Date table keeps track of all the important dates, like when transactions happen or when invoices are due. It connects this date information to
other tables, like General Ledger and Invoices, to help organise and analyze financial data based on dates.
3. Build relationships between different tables to show data connections.
1. Account to Account Sort
• Join Type: Many-to-One
• Key: Account
• Description: This relationship links each account to multiple records in the Account Sort table, allowing financial data or transaction details to be
associated with the corresponding account.
2. Customer to Company
• Join Type: Many-to-One
• Key: Company
• Description: This connection links customers to a single company, allowing customers to be associated with the company they belong to.
3. General Ledger to Account
• Join Type: Many-to-Many
• Key: Account → AccountJoin
• Description: This relationship connects general ledger entries to multiple accounts, enabling the tracking of financial transactions across various
accounts.
4. General Ledger to Company
• Join Type: Many-to-One
• Key: Company
• Description: This relationship associates general ledger entries with a specific company, linking financial records to the corresponding company.
5. General Ledger to Date
• Join Type: Many-to-One
• Key: Date
• Description: This connection links general ledger entries to specific dates, enabling the organization of financial data by transaction date.
6. Invoice to Date
• Join Type: Many-to-One
• Key: TxnDate → Date
• Description: This relationship connects invoices to specific transaction dates, allowing each invoice to be linked to its corresponding date of issuance or
payment.
7. Customer to invoice
• Join Type: Many-to-Many
• Key: Id → CustomerRef.value
• Description: This relationship links customers to their invoices so you can easily see which invoices belong to each customer. It helps manage and
track all invoices and payments for every customer.
8. Ap Aging to Company
• Join Type: Many-to-One
• Key: Company
• Description: This relationship connects financial data to specific dates, helping the company keep track of payments and organize its financial records
by date.
9. Ap Aging to Vendor
• Join Type: Many-to-Many
• Key: Vendor
• Description: This relationship connects vendor-related financial data to specific dates, making it easier to track vendor payments and manage finances
by date.
4. Create Visualisations to display important information in charts, tables, and maps. Your dashboard should match the below layout.
Financial dashboard
