In Power BI, you can calculate the non-standard sales cycles with the DAX formula using time intelligence functions, such as TOTALYTD, DATEDIFF, etc. These functions let you calculate sales over a rolling period of time.
In this article, I will walk you through the steps to use DAX formulas to calculate non-standard sales cycles in Power BI.
Let’s get started.
What are Non-Standard Sales Cycles?
Non-standard sales cycles are sales cycles that do not follow the traditional and predictable patterns of sales. In a non-standard sales cycle, for example, a sale may take longer than usual to close or involve multiple decision-making stages.
While analyzing non-standard cycles can be challenging, with the right techniques, you can gain valuable insights into your sales performance.
Thus, using DAX formulas in Power BI is one effective way to calculate and analyze non-standard sales cycles.
To calculate the non-standard sales cycles using the DAX formula, you can follow these steps:
Create a Date Table
The first step is to create a date table, as this will help you to analyze sales data by date and track the duration of sales cycles.
This is because, to use time intelligence functions in DAX, you need to have a date table in your data that covers the entire date range in your data.
Now, to create a date table, go to the Modeling tab and click on New Table. Then enter the formula:
DateTable = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))NB: Replace “Sales[Date]” with the name of the date column in your data model that you want to use as the basis for your date table.
Also, you can customize start and end dates if you want to analyze data for a specific time period. To do this, you can use this formula:
DateTable = CALENDAR(DATE(2021,5,1), DATE(2022,5,31))This formula will create a table with a list of dates from May 1, 2021 – May 31, 2022.
Calculate the Length of the Sales Cycle
In every business, one of the key metrics is the length of their sales cycle which is the amount of time it takes for a potential to become a customer.
So, to calculate this, you will create measures that subtract the date a lead was created from the date they became a customer.
You will then use these measures to analyze your sales cycle. Here are a few examples of the Length of Sales Cycle:
Qualification Stage
In this cycle stage, the formula calculates the time between first contact and product qualification.
Qualification = CALCULATE( MIN('DateTable'[Date]) - MIN(Sales[Date]), Sales[Stage] = "Qualification" )Needs Analysis Stage
During this stage, the formula calculates the duration between the customer’s qualification date and their needs analysis.
Needs_Analysis = CALCULATE( MIN('DateTable'[Date]) - MIN(Sales[Date]), Sales[Stage] = "Needs Analysis" )Negotiation Stage
In this stage, the formula calculates the duration between the date a customer’s needs are analyzed and the date of the negotiation.
Negotiation NegotiationClosing Stage
During this stage, the formula will calculate the duration between the time in presentations and the date of the sale.
Closing = CALCULATE( MIN('DateTable'[Date]) - MIN(Sales[Date]), Sales[Stage] = "Closing" )Create a Calculated Column to Assign the Stages
Now, to calculate non-standard sales cycles, you will assign each sale to a stage based on the date it was closed. To do this, go to “Modeling” in the ribbon, click “New Column,” and enter the following formula:
Stage =
SWITCH(TRUE(),
Sales[Date] < DATE(2021, 1, 1), "Qualification",
Sales[Date] < DATE(2021, 4, 1), "Needs_Analysis",
Sales[Date] < DATE(2021, 7, 1), "Negotation",
Sales[Date] >= DATE(2021, 7, 1), "Closing"
)Replace the dates in this formula with the dates that mark the transition between each stage of your sales cycle.
DAX Formula to Calculate Non-standard Sales Cycles
Finally, to calculate the non-standard sales cycle in Power BI, you can use the following DAX formula:
Non-Standard Sales Cycle =
VAR MaxDate = MAX(Sales[Date])
VAR MinDate = MIN(Sales[Date])
VAR TotalDays = MaxDate - MinDate
RETURN
SUMX(
VALUES(Stage[Stage]),
DATEDIFF(
CALCULATE(MIN(Sales[Date]), FILTER(ALL(Sales), Sales[Stage] = EARLIER(Stage[Stage]))),
CALCULATE(MIN(Sales[Date]), FILTER(ALL(Sales), Sales[Stage] = EARLIER(Stage[Stage]) && Sales[Date] >= MinDate)),
DAY
) / TotalDays
)This formula uses the DATEDIFF function to calculate the number of days a deal spends in each stage, and then divide that by the total length of the sales cycle (in days) to get a percentage.
It then sums up these percentages for each stage, resulting in a total percentage for the non-standard sales cycle based on the Length of the Sales Cycle.
FAQs
What is the purpose of calculating a Non-standard sales cycle?
Calculating a non-standard sales cycle can help business managers understand the length of their sales cycle and identify areas of improvement in their sales process.
It can also help them make more accurate revenue forecasts and allocate resources more effectively.
Can the DAX formula be customized for different sales cycles?
Yes, you can also customize DAX formulas for different sales cycles by changing the conditions in the SWITCH function.
How to define the dates of your non-standard sales cycle?
Based on your business needs, you can define the dates for calculating your non-standard sales cycle.
For instance, you may decide to start your sales cycle on the first day of a certain month. Also, you can make use of your fiscal year.
What is DAX?
DAX (Data Analysis Expressions) is a formula language. It is then used to define custom calculations for data analysis and modeling.
Conclusion
Once you define these measures, you can add them to your visuals to analyze your non-standard sales cycle.
You can also modify the formulas as needed to calculate other measures, such as quarterly or monthly sales.
If you enjoyed reading this article, you can also check how to calculate the cumulative value from a start date in Power BI.
Happy Analyzing!