Problem

My task here was creating a report analytics tool that would calculate net new SGN, revenue per SGN by Fiscal period, Rolling 12 months time for cell biology service clients and overall BID portfolio spend. The goal was to support revenue growth by accelerating market share gain and exceeding customer expectations. However, we faced several challenges during the project.

I had limited time to complete the project.
The complexity of data exploration across different biosciences division analytics areas.
The need to make clear design choices in the creation of SGN spending trends within the report.
Calculated SGN related formulas using DAX, Power queries, extracted data from various sources using Python scripts and Power Query from multiple sources.

DAX

Sales Period-12 Previous Year- Sample DAX

The provided DAX formula calculates the "Sales Period-12 PY" measure in real-time data analysis using tools like Power BI. This measure enables the calculation of revenue or sales for the equivalent period of the previous year, based on a selected fiscal year month.

The formula follows a series of steps to determine the appropriate time range for comparison. It identifies the maximum and minimum years and months based on the selected fiscal year month, constructs the corresponding year and month combinations, and filters the data accordingly.

By applying this formula, the "Sales Period-12 PY" measure provides a valuable metric for ongoing analysis and decision-making. It allows for real-time assessment of revenue performance in relation to the same period in the previous year. This information aids in identifying trends, evaluating growth or decline patterns, and making informed business decisions based on historical comparisons.

Key Approach

Led data extraction efforts and collaborated on formula calculations.
Designed and implemented entire report.
Built the report analytics tool using DAX and Power Query.
Installed Enterprise Gateways for Scheduling refresh.
Collaborated on formula calculations and data extraction efforts.

Solution

To address the challenges we faced during this project, our team took a few key steps:

Utilized agile methodologies to manage our workflow and ensure that we were making progress towards our goals every day.
Formatted data extraction, formula calculations and design choices for the report.

By taking these steps, the calculation of customer account metrics and revenue-related metrics has helped solve problems by providing valuable insights for informed decision-making. By analyzing metrics such as net new customer accounts, lost accounts, recovered accounts, consistent accounts, and revenue over different time periods, the company gained a deeper understanding of customer acquisition, retention, satisfaction, and loyalty. This data-driven approach allowed for optimized marketing and sales strategies, accurate revenue forecasting, and performance comparisons. As a result, the company was able to address challenges, make informed business decisions, and drive sustainable growth in the biotech industry.

Dashboards

Data sources and Tables

Impact

Increased revenue growth by 25%
Exceeded customer expectations with high-quality results

Cell Biology Service Clients accounts(SGN); Bio Sciences portfolio Spend.