Forecast with DAX

[cbtekrony]

I am exhausted from trying to get this solution. I have data that is a customized forecast. I have created a dummy below. I am trying to calculate the projected future growth based on the estimated growth so far in 2020. I prefer to have this in a table with YTD sales. My problems are: 1) I cannot get future years to show in my table, even though I have up to 2025 in my date table and chose to include rows without data. 2) I have gotten the projected 2021 growth number to show up in my PowerBI visualization table, but it's showing on the 2020 row instead.  See my excel data below: 

Year	YTD Sales	YOY change	Projected Growth based on 2020 grwth
2016	$6,553,525
2017	$7,571,282	15.5%
2018	$8,999,105	18.9%
2019	$10,069,233	11.9%
2020	$    10,794,544	8.6%	$10,939,606	This is based on YOY Change * YTD Sales
2021			$11,885,214	Based on Cell Above Grwth * YOY Change
2022			$12,912,559	Based on Cell Above Grwth * YOY Change
2023			$14,028,707	Based on Cell Above Grwth * YOY Change
2024			$15,241,334	Based on Cell Above Grwth * YOY Change
2025			$16,558,778	Based on Cell Above Grwth * YOY Change

[cbtekrony]

@DataInsights , my data is subject to filtering by Product. I have a slicer set up with about 15 products. I click on a single product name,, and the forecast data disappears (only data through 2020 shows). Once I remove any filters for Product, the forecast through 2025 reappears.  Any ideas?

Thank you for sticking with this.

[cbtekrony]

@DataInsights 

Here are images: 

No filter:

Filter:

[DataInsights]

@cbtekrony,

Try selecting the blank value in addition to the Product(s) you want in your slicer.

[cbtekrony]

Can you clarify what you mean by blank value?

[DataInsights]

@cbtekrony,

See slicer below:

[cbtekrony]

@DataInsights 

Ok, that is what I thought you meant. It didn't work, but what did work (and it somewhat of a pain - but it works), is I created a Filter table that I will have to recreate for each product in the slicer. Then, I copied the measures, and updated your Project Growth measure to reflect the correct data. Here is what I used to create the filter table in case you are curious:

Product Filter Table = FILTER('PBI Sample Data2', 'PBI Sample Data2'[Product] = "Product Name")

In addition, I am attempting to use your provided calculation to calculate the future with the average growth rate (which I have already calculated) over time. Here is what I plugged in to your formula for the growth rate but it is not working:

VAR vGrowthRate =

AVERAGEX(

    KEEPFILTERS(VALUES('Calendar Table'[Year])),

    CALCULATE([AS YoY Change])

)

When I plug in this formula, the result forecast for each year $10,995,139 - so it is not compounding the growth rate for the future years. When I type the percentage growth in the DAX it calculates correctly, but of course I want this percentage to be dynamic.

Thank you again for your help.

[cbtekrony]

@DataInsights ,

Will this work?

https://www.dropbox.com/s/156x66f4h3xbh50/Projections%20Sample%20File.pbix?dl=0

[DataInsights · Accepted solution]

@cbtekrony,

In your PROJECTED GRWTH measure, the second and third variables compare a date to a year. Change it to the following and it should work:

PROJECTED GRWTH = 
VAR vCurYear =
    CALCULATE ( YEAR ( MAX ( 'PBI Sample Data2'[Process Date] ) ), ALL ('PBI Sample Data2') )
VAR vCurYearSales =
    CALCULATE ( SUMX ( 'PBI Sample Data2', 'PBI Sample Data2'[ACV+YTD Est.] ),  'Calendar Table'[Year] = vCurYear )
VAR vLastYearSales =
    CALCULATE (SUMX (  'PBI Sample Data2', 'PBI Sample Data2'[ACV+YTD Est.] ),  'Calendar Table'[Year] = vCurYear - 1 )
VAR vGrowthRate =
    DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )
VAR vYearIncrement =
    MAX ( 'Calendar Table'[Year] ) - vCurYear
VAR vProjGrowth =
    vCurYearSales
        * POWER ( 1 + vGrowthRate, vYearIncrement )
VAR vResult =
    IF ( MAX ( 'Calendar Table'[Year] ) <= vCurYear, BLANK (), ROUND ( vProjGrowth, 0 ) )
RETURN
    vResult

[ebhomium]

CAN ANYONE HELP WITH STEPS ON HOW TO RUN A FORECAST FOR YEAR 2023, 2024,2025, 2026 ON POWER BI.CHECK MY SCREENSHOT BELOW.

[cbtekrony]

Yes! It works!!! Thank you Thank you Thank you!!!! 😀

[cbtekrony]

@DataInsights

I have a sample file...how do I attach?

[DataInsights]

@cbtekrony,

See below from Community Support Team:

For some new community members, they don't have the permission to upload .pbix files. You may need to share file links via DropBox, One Drive, Drive or any other tool.

[cbtekrony]

Still not working. Here is my date table DAX. I have it going all the way to 2025, and it is connected to the sales date and dollars in the other table. I even tried to add "dummy dates" to my other table and it still would not pull in the future years in my visualization. 

Calendar Table = VAR BaseCalendar = CALENDAR (DATE(2012, 1, 1), DATE(2025, 12, 31)) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR (BaseDate) VAR MonthNumber = MONTH (BaseDate) VAR YearMonthNumber = YearDate * 12 + MonthNumber -1 VAR QTR = CONCATENATE("Q",ROUNDUP(MONTH([Date])/3,0)) RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT (BaseDate, "mmmm"), "Year Month Number", YearMonthNumber, "Year Month", FORMAT (BaseDate, "mmm yy"), "QTR", QTR))

I feel like I am missing an "IFISBLANK" operator somewhere for the current year sales...

[DataInsights]

@cbtekrony,

Would you be able to share your pbix? You can replace sensitive data with sample data.

[cbtekrony]

Yes let me work on this and I will lob it your way in a few days!

[cbtekrony]

Thank you, I got the Year and YTD Sales to work....but not the Projected Growth. My data ends at year 2020. I am not doing something correct with my dates to that future years are showing up. Also is ( SalesProjection[Date] ) supposed to pull from my table where I have my sales numbers? And, ( Dates[Year] ) pull from my date table? I'm feeling that I do not have my dates set up correctly. 

[DataInsights]

@cbtekrony,

Do you have a date slicer filtering the visual? If so, you may need to disable interactions between the slicer and the visual, or select 2016 - 2025 in the slicer. Hover over the filter icon in the top-right of the visual to see what filters are in effect.

Yes, SalesProjection[Date] should pull from the table where you have your sales numbers, and Dates[Year] should pull from your date table. Be sure that these two tables have a relationship (use the Date column in each table for the relationship). The column Dates[Year] should be used in the visual’s rows.

[DataInsights]

@cbtekrony,

Would you clarify the following?

1. 2020 YOY change: (10,794,544 - 10,069,233) / 10,069,233 = 7.2%; the example shows 8.6%

2. How is Projected Growth of 10,939,606 calculated? This should be on line 2021, right?

[cbtekrony]

10,939,606 is a custom calculation based on actual ytd sales + expected sales through the end of the year. It should really be in the YTD column. Sorry I had to recreate this in Excel. It should read:

Year YTD Sales YOY change Projected Growth based on 2020 grwth

2020  $10,939,606  8.6%  

2021                                        $11,885,214         This is based on YOY Change * YTD Sales previous yr

2022                                        $12,912,559         Based on Cell Above Grwth * YOY Change

....and so on

[DataInsights]

@cbtekrony,

Try this measure:

Projected Growth = 
VAR vCurYear =
    CALCULATE ( YEAR ( MAX ( SalesProjection[Date] ) ), ALL ( SalesProjection ) )
VAR vCurYearSales =
    CALCULATE ( SUM ( SalesProjection[YTD Sales] ), Dates[Year] = vCurYear )
VAR vLastYearSales =
    CALCULATE ( SUM ( SalesProjection[YTD Sales] ), Dates[Year] = vCurYear - 1 )
VAR vGrowthRate =
    DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )
VAR vYearIncrement =
    MAX ( Dates[Year] ) - vCurYear
VAR vProjGrowth =
    vCurYearSales
        * POWER ( 1 + vGrowthRate, vYearIncrement )
VAR vResult =
    IF ( MAX ( Dates[Year] ) <= vCurYear, BLANK (), ROUND ( vProjGrowth, 0 ) )
RETURN
    vResult