Create Dynamic CRM Forecast From Today Until End Of Month

In this tutorial, I’m going to show you how to create a dynamic total CRM forecast within a specified date range. You may watch the full video of this tutorial at the bottom of this blog.

Let’s say for example, you want to find out the forecast in a particular month. You want to know how many forecasts there are for the rest of the month from today until the monthâ€™s end. As you move from one day to the next, the CRM forecast amount is going to change.

You want to be able to dynamically calculate this total forecast within the beginning and end date you’ve specified.

This technique is highly relevant for CRM systems especially when youâ€™re trying to manage monthly targets. Youâ€™ll see what you need to achieve from a sales and team perspective.

Calculating Total CRM Forecast

When trying to work this out, it’s possible you might get confused with the context of your calculation.

Because you want to look at a particular month at any point in time, the model should be dynamic.

So, for this particular case, you need to get rid of the context coming from the Dates. Instead, you should apply the context within the formula.

1. Build DateRange

Letâ€™s look at the formula:

The solution is to simply build a DateRange. You need to create a DateRange and context within the formula. You need to build it using Variables.

2. Use TODAY Function

You keep the formula dynamic by utilizing the TODAY function.

Itâ€™ll always return the date today.

Youâ€™re also be able to work out what the End Of The Month date is by using TODAY.

So, I now have StartDay and EndDay. This gives the desired beginning and end date within the DateRange.

3. Use The DATESBETWEEN Function

Using the time intelligence function DATESBETWEEN enables you to open up a dynamic window.

Iâ€™m going to use the previous variables that Iâ€™ve created within the DateRange variable. So, itâ€™s simply referencing variables within variables.

Variables can be very effective to use because you can layer them.

So, within this part of the formula, thereâ€™s now a time intelligence function DATESBETWEEN which gives us a DateRange between Today, the Current Date, and the Month based on Today. This will adjust from a day-to-day perspective.

At the bottom of the formula, thereâ€™s the RETURN function. This is where you need to use CALCULATE.

The CALCULATE function can change the context of the calculations.

For instance, Total Sales Forecasts are all of these particular results shown in the table.

These results are currently filtered by every single Date in the table regardless of the multiple months in the data.

So now, you need to accumulate and count up the amount of Sales within the date window.

This is done using the CALCULATE function.

All you need to do is input the variable DateRange inside CALCULATE.

This date window has changed the context of the calculation. Youâ€™re now only calculating Total Sales Forecasts from today until the end of the current month.

Conclusion

This solution is great to use because of its simplicity. Itâ€™s a seamless and intuitive technique using the CALCULATE function along with the time intelligence functions inside it.

This tutorial highlights very relevant concepts like the context, the CALCULATE function, and the time intelligence functions.

These three combined are crucial elements in your models. You must consistently combine them well inside Power BI to get good insights out of your reports and data scenarios.

All the best,

Sam

How To Install DAX Studio & Tabular Editor In Power BI

In this tutorial, you'll learn how to download and install DAX Studio and Tabular Editor 3 in your...

This tutorial will guide us on how we can download and install R and RStudio which are both free and...

Launching The Enterprise DNA Membership License

Today we launch a brand new offering from Enterprise DNA.  After a tremendous...

Iterating Functions In DAX Language – A Detailed Example

For this blog post, I want to dive into iterating functions within the DAX language in Power BI. The...

Using Power BI DAX Functions To Deal With Products That Have Changing Prices Overtime

In this blog post, we are going to work through an advanced but real-world analysis involving Power BI...

Power BI Dashboard Designs: Visuals And Effects

An effective dashboard design presents data in a concise, engaging, and powerful way. The presentation...

DAX Measures In Power BI Using Measure Branching

Build DAX measures using measures. This is what I call measure branching. This technique is one of the...

Temporal Scale Using Calculated Columns In Power BI

Every so often weâ€™ll be needing the availability of a custom visual of a bar chart or line chart that...

First N Business Days Revisited – A DAX Coding Language Solution

Let's take another look at the problem discussed in this tutorial, which dynamically compared the first...

Junk Dimension: What Is It And Why It’s Anything But Junk

Today, I want to talk about a data modeling concept called junk dimension. From its name, you'd think...

Power BI Slope Chart: An Overview

In this tutorial, we'll be looking at a not-so-common custom visual called the Power BI slope chart....

Create Power BI Reports With These Techniques & Examples

For todayâ€™s blog, I want to walk through a couple of amazing reports and share some of the tips I have...