Financial Analysis using Power BI

#analytics #datascience #business #finance

I have been hired as a Freelance Data Analyst to help review a data and provide insights that would be valuable to the CFO of this organization. The business has been performing well and the management wants to get insights into their 2013 and 2014 performances.

The Business Questions

Through internal brainstorming, the CFO has confirmed that they needed answers to the following questions:

What is the company's performance in 2014 compared to 2013?
Which countries drove the orders and profit margins of the company in the two years period?
What was the trend in the company's sales over the two years period?
The profitability of each segments.
What were the order of countries in terms of the quantity of products sold?

To answer these questions, the company has provided us with their financial data for 2013 and 2014. I was required to design an interactive report with the insights I will discover from the analysis.

Tasks

I am going to model the data and perform analysis using DAX. This is the outline of the requirements:

Create a Date Table using the CALENDARAUTO function. This will support the time intelligence analysis.
Create a relationship between the financial table and the newly created Date Table using the Date columns in both Tables.
Using DAX, I am going to create measures to answer the business questions. Some of these measures include: Total orders, Total Sales Amount, Total Discount offered, Total Profit, Profit Margin etc.
Perform time intelligence analysis by creating measures that calculate the same measures in the requirement above for the previous year (2013).

ANALYSIS

A Date Table was created with new columns that include Month and Year as seen in the image below;
Both the business datasets and date table were modelled to create a relationship.
DAX functions were written to create measures for EACH of the analysis. An example is shown in the image below, to calculate the Total Orders made;
New measures were also created to find the SAME metrics for the previous year. Note that "LY" means Last Year. An example of the Total Revenue for LAST YEAR is shown in the image below;