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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cbtekrony
Resolver I
Resolver I

Forecast with DAX

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: 

 
YearYTD SalesYOY changeProjected Growth based on 2020 grwth
2016$6,553,525   
2017$7,571,28215.5%  
2018$8,999,10518.9%  
2019$10,069,23311.9%  
2020 $    10,794,5448.6%$10,939,606This is based on YOY Change * YTD Sales
2021  $11,885,214Based 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
 
 
 
 

 

1 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

 





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

Proud to be a Super User!




View solution in original post

21 REPLIES 21
cbtekrony
Resolver I
Resolver I

@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.

 

@DataInsights 

Here are images: 

No filter:

cbtekrony_0-1610632184982.png

Filter:

cbtekrony_2-1610632358093.png

 

@cbtekrony,

 

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





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

Proud to be a Super User!




Can you clarify what you mean by blank value?

@cbtekrony,

 

See slicer below:

 

DataInsights_0-1611070420696.png

 





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

Proud to be a Super User!




@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
Resolver I
Resolver I

@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

 





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

Proud to be a Super User!




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

 

 

 

TO FORCAST.jpg

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

cbtekrony
Resolver I
Resolver I

@DataInsights

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

@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.





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

Proud to be a Super User!




cbtekrony
Resolver I
Resolver I

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...

@cbtekrony,

 

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





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

Proud to be a Super User!




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

cbtekrony
Resolver I
Resolver I

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. 

@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.





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

Proud to be a Super User!




DataInsights
Super User
Super User

@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?





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

Proud to be a Super User!




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

 

@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

 

DataInsights_0-1607312686277.png

 





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

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.