Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to Solution.
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
Proud to be a Super User!
@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.
Try selecting the blank value in addition to the Product(s) you want in your slicer.
Proud to be a Super User!
Can you clarify what you mean by blank value?
See slicer below:
Proud to be a Super User!
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:
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
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.
Yes! It works!!! Thank you Thank you Thank you!!!! 😀
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.
Proud to be a Super User!
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.
Would you be able to share your pbix? You can replace sensitive data with sample data.
Proud to be a Super User!
Yes let me work on this and I will lob it your way in a few days!
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.
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.
Proud to be a Super User!
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?
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
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
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |