{"id":2414,"date":"2023-03-03T10:31:58","date_gmt":"2023-03-03T10:31:58","guid":{"rendered":"https:\/\/monocroft.com\/?p=2414"},"modified":"2023-04-08T02:30:40","modified_gmt":"2023-04-08T02:30:40","slug":"dax-formula-to-calculate-non-standard-sales-cycles","status":"publish","type":"post","link":"https:\/\/monocroft.com\/dax-formula-to-calculate-non-standard-sales-cycles\/","title":{"rendered":"DAX Formula to Calculate Non-standard Sales Cycles"},"content":{"rendered":"\n
In Power BI, you can calculate the non-standard sales cycles with the DAX formula using time intelligence functions, such as Let’s get started.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n While analyzing non-standard cycles can be challenging, with the right techniques, you can gain valuable insights into your sales performance. <\/p>\n\n\n\n Thus, using DAX formulas in Power BI is one effective way to calculate and analyze non-standard sales cycles.<\/p>\n\n\n\n To calculate the non-standard sales cycles using the DAX formula, you can follow these steps:<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Now, to create a date table, go to the Modeling<\/strong> tab and click on New Table<\/strong>. Then enter the formula:<\/p>\n\n\n\n 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.<\/em><\/p>\n\n\n\n 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:<\/p>\n\n\n\n This formula will create a table with a list of dates from May 1, 2021 – May 31, 2022.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n So, to calculate this, you will create measures that subtract the date a lead was created from the date they became a customer. <\/p>\n\n\n\n You will then use these measures to analyze your sales cycle. Here are a few examples of the Length of Sales Cycle:<\/p>\n\n\n\n In this cycle stage, the formula calculates the time between first contact and product qualification.<\/p>\n\n\n\n During this stage, the formula calculates the duration between the customer’s qualification date and their needs analysis.<\/p>\n\n\n\n In this stage, the formula calculates the duration between the date a customer’s needs are analyzed and the date of the negotiation.<\/p>\n\n\n\n During this stage, the formula will calculate the duration between the time in presentations and the date of the sale.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n Replace the dates in this formula with the dates that mark the transition between each stage of your sales cycle.<\/em><\/p>\n\n\n\n Finally, to calculate the non-standard sales cycle in Power BI, you can use the following DAX formula:<\/p>\n\n\n\n This formula uses the DATEDIFF <\/strong>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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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. <\/p>\n\n\n\n It can also help them make more accurate revenue forecasts and allocate resources more effectively.<\/p>\n\n\n\n Yes, you can also customize DAX formulas for different sales cycles by changing the conditions in the SWITCH <\/strong>function.<\/p>\n\n\n\n Based on your business needs, you can define the dates for calculating your non-standard sales cycle.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n DAX (Data Analysis Expressions) is a formula language<\/strong>. It is then used to define custom calculations for data analysis and modeling.<\/p>\n\n\n\n Once you define these measures, you can add them to your visuals to analyze your non-standard sales cycle.<\/p>\n\n\n\n You can also modify the formulas as needed to calculate other measures, such as quarterly or monthly sales.<\/p>\n\n\n\n If you enjoyed reading this article, you can also check how to calculate the cumulative value from a start date in Power BI<\/a>.<\/p>\n\n\n\n Happy Analyzing!<\/p>\n","protected":false},"excerpt":{"rendered":" In Power BI, you can calculate the non-standard sales cycles with the DAX formula using time intelligence functions, such as … <\/p>\nTOTALYTD<\/strong><\/code>,
DATEDIFF<\/strong><\/code>, 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.<\/p>\n\n\n\nWhat are Non-Standard Sales Cycles?<\/h2>\n\n\n\n
Create a Date Table<\/h2>\n\n\n\n
DateTable = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))<\/code><\/strong><\/pre>\n\n\n\n
DateTable = CALENDAR(DATE(2021,5,1), DATE(2022,5,31))<\/code><\/strong><\/pre>\n\n\n\n
Calculate the Length of the Sales Cycle<\/h2>\n\n\n\n
Qualification Stage<\/h3>\n\n\n\n
Qualification = CALCULATE( MIN('DateTable'[Date]) - MIN(Sales[Date]), Sales[Stage] = \"Qualification\" )<\/code><\/strong><\/pre>\n\n\n\n
Needs Analysis Stage<\/h3>\n\n\n\n
Needs_Analysis = CALCULATE( MIN('DateTable'[Date]) - MIN(Sales[Date]), Sales[Stage] = \"Needs Analysis\" )<\/code><\/strong><\/pre>\n\n\n\n
Negotiatio<\/strong><\/strong>n Stage<\/h3>\n\n\n\n
Negotiation <\/code><\/strong><\/code><\/strong>= CALCULATE( MIN('DateTable'[Date]) - MIN(Sales[Date]), Sales[Stage] = \"
Negotiation<\/code><\/strong><\/code><\/strong>\" )<\/code><\/strong><\/pre>\n\n\n\n
Closing Stage<\/h3>\n\n\n\n
Closing = CALCULATE( MIN('DateTable'[Date]) - MIN(Sales[Date]), Sales[Stage] = \"Closing\" )<\/code><\/strong><\/pre>\n\n\n\n
Create a Calculated Column to Assign the Stages<\/h2>\n\n\n\n
Stage = \n SWITCH(TRUE(),\n Sales[Date] < DATE(2021, 1, 1), \"
Qualification<\/code><\/strong>\",\n Sales[Date] < DATE(2021, 4, 1), \"
Needs_Analysis<\/code><\/strong>\",\n Sales[Date] < DATE(2021, 7, 1), \"
Negotation<\/code><\/strong>\",\n Sales[Date] >= DATE(2021, 7, 1), \"Closing\"\n )<\/code><\/strong><\/pre>\n\n\n\n
DAX Formula to Calculate Non-standard Sales Cycles<\/h2>\n\n\n\n
Non-Standard Sales Cycle =\nVAR MaxDate = MAX(Sales[Date])\nVAR MinDate = MIN(Sales[Date])\nVAR TotalDays = MaxDate - MinDate\nRETURN\nSUMX(\n VALUES(Stage[Stage]),\n DATEDIFF(\n CALCULATE(MIN(Sales[Date]), FILTER(ALL(Sales), Sales[Stage] = EARLIER(Stage[Stage]))),\n CALCULATE(MIN(Sales[Date]), FILTER(ALL(Sales), Sales[Stage] = EARLIER(Stage[Stage]) && Sales[Date] >= MinDate)),\n DAY\n ) \/ TotalDays\n)<\/code><\/strong><\/pre>\n\n\n\n
FAQs<\/h2>\n\n\n\n
What is the purpose of calculating a Non-standard sales cycle?<\/h3>\n\n\n\n
Can the DAX formula be customized for different sales cycles?<\/h3>\n\n\n\n
How to define the dates of your non-standard sales cycle?<\/h3>\n\n\n\n
What is DAX?<\/h3>\n\n\n\n
Conclusion<\/h2>\n\n\n\n