Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating sales forecast based on current sales

In my dashboard, I am trying to show what the projected sales are for this year based on actual sales and remaining forecast. For example, for any activity through April 2017, I would add up the forecast May 2017 to December 2017 and then add to it any actual sales Jan 2017 through April 2017.

 

Using a standard date table, a sales extract and a forecast sheet in excel I created the following formulas:

1) Get the last day the sale occured this year to get a current date to work with:


CurrentDate = CALCULATE(max(autoCalendar[Date]),filter(sales,sales[Total_Sales]>0))

 

2) Get the remaining 8 months sales forecast

 

Forecast Balance = CALCULATE(sum(Forecast[Forecast]),FILTER(Forecast,Forecast[Forecast_Date]> [CurrentDate]))

 

3) Add the two to get projected sales

 

Project sales = [This Year Sales]+[Forecast Balance]

 

I am sure all 3 steps can be done in 1. The problem I have is that the forecast balance is picking up the forecast for Jan 2017.

The correct numbers would be: Forecast Balance = 25500 and I get 26600

 

What is wrong with the formula in step # 2? Any help is appreciated!

 

Display to test dataDisplay to test dataForecast FileForecast File

1 ACCEPTED SOLUTION
mattbrice
Solution Sage
Solution Sage

This might work:

 

Forecast Balance =
VAR CurrentDate =
    CALCULATE (
        LASTNONBLANK ( autoCalendar[Date], [Total Sales] ),
        ALL ( autoCalendar[Year-Month] )
    )
RETURN
    CALCULATE (
        SUM ( Forecast[Forecast] ),
        FILTER ( VALUES ( Forecast[Date] ), Forecast[Forecast_Date] > CurrentDate )
    )

 

This Year Sales =
VAR CurrentDate =
    CALCULATE (
        LASTNONBLANK ( autoCalendar[Date], [Total Sales] ),
        ALL ( autoCalendar[Year-Month] )
    )
RETURN
    TOTALYTD ( SUM ( sales[Total_Sales] ), CurrentDate )

then finally

Project sales = [This Year Sales]+[Forecast Balance]

 

 

EDIT:  Thinking about it, since the only slicer on page seems to be year, you can reduce the CurrentDate VAR to just:

 

CurrentDate = LASTNONBLANK ( autoCalenar[Date], [Total Sales] )

 assuming 

 

Total Sales = SUM ( sales[Total_Sales] )

View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @Anonymous,

 

I have made some tests and believe that your issue is regarding the dates that you have in the Calendar, Sales and Forecast, since the forecast dates are only the first day of each month when you calculate the values based on the Current Date it will get you the values for current month in the forecast total.

 

I have made a couple of adjustments to your formulas and have the following result:

 

1)  CurrentDate change by column with this formula

Current_Date = IF(
CALCULATE(MAX(AutoCalendar[Date]),
FILTER(Sales,Sales[Sales]>0))>=AutoCalendar[Date],AutoCalendar[Date],
BLANK())

This allow you to make all the dates to show if they are below the current date

 

3) Forescasted value changed to the one below taking into account the previous column

Forecast Balance = CALCULATE(
sum(Forecast[Forecast]),
FILTER(AutoCalendar,ISBLANK(AutoCalendar[Current_Date])))

This will return all the row in the Forecast that have the blank rows that is what you need to sum.

 

4) Projectd sales with the new calculations

Projected Sales = [This Year Sales]+Forecast[Forecast Balance]

See print below if result is as you need.

Project_sales.png

 

 

 

Hope this helps

 

Regards,

 

MFelix

 

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix Yes the results in your output match what I am looking for. And yes you are also correct that the Forecast sheet only has single dates for monthly forecast. I did think that was an issue as 1/1/2017 has a forecast, but no sales were billed on that day.

 

I get an error when trying to build the current date using your formula: I believe it is related to comparing the result of max() to autocalendar[date]. In fact, the only options that came up when typing autocalendar were measures that I had created. I think the expression is expecting a single value when doing a comparison.  

 

Here is the error:

"A single value for column "date" in table "autocalendar" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min,max,count or sum to get a single result"

pbi_3.JPG

 

@Anonymous,

Are you creating a measure or a column? It needs to be a column.

Regards

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix  ah that makes sense.  I was creating it as a measure. Thanks for the clarification.

mattbrice
Solution Sage
Solution Sage

This might work:

 

Forecast Balance =
VAR CurrentDate =
    CALCULATE (
        LASTNONBLANK ( autoCalendar[Date], [Total Sales] ),
        ALL ( autoCalendar[Year-Month] )
    )
RETURN
    CALCULATE (
        SUM ( Forecast[Forecast] ),
        FILTER ( VALUES ( Forecast[Date] ), Forecast[Forecast_Date] > CurrentDate )
    )

 

This Year Sales =
VAR CurrentDate =
    CALCULATE (
        LASTNONBLANK ( autoCalendar[Date], [Total Sales] ),
        ALL ( autoCalendar[Year-Month] )
    )
RETURN
    TOTALYTD ( SUM ( sales[Total_Sales] ), CurrentDate )

then finally

Project sales = [This Year Sales]+[Forecast Balance]

 

 

EDIT:  Thinking about it, since the only slicer on page seems to be year, you can reduce the CurrentDate VAR to just:

 

CurrentDate = LASTNONBLANK ( autoCalenar[Date], [Total Sales] )

 assuming 

 

Total Sales = SUM ( sales[Total_Sales] )
Anonymous
Not applicable

That worked!  Forecast is now showing the correct values. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.