Forum Discussion
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:
| 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 |
Anonymous,
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
21 Replies
Anonymous,
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?
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
Anonymous,
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
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.
Anonymous,
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.
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...Anonymous,
Would you be able to share your pbix? You can replace sensitive data with sample data.
I have a sample file...how do I attach?
Anonymous,
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.
Anonymous,
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
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.
Anonymous,
Try selecting the blank value in addition to the Product(s) you want in your slicer.