Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Experts,
I am trying to forecast with very few data points. So I need to go the Cumulative Totals way, the way we do in excel.
Created a sample for you below:
Month | Actual Sales | Cumulative Sales | Days in Month | Cumulative Days in Month | Forecast Sales | Explanation | Result Column |
Jan 2019 | 120 | 120 | 31 | 31 | 120 | 120 | |
Feb 2019 | 97 | 217 | 28 | 59 | 217 | 217 | |
Mar 2019 | 135 | 352 | 31 | 90 | 352 | 352 | |
Apr 2019 | 82 | 434 | 30 | 120 | 434 | 434 | |
May 2019 | 434 | 31 | 151 | (434/120)*151 | IF Col 'B' Isblank then Max(Cumulative Sales)/Cumulative Days of Last Non Blank Actual Total Month * Cumulative Days of Current Blank Month | 546.1 | |
Jun 2019 | 434 | 30 | 181 | (434/120)*181 | 654.6 |
Solved! Go to Solution.
Hi @cmgovind ,
I created a calculated column to implement it that you can reference.
Forecast Sales = var max_days = CALCULATE(MAX('Table'[Cumulative Days in Month]),FILTER('Table','Table'[Actual Sales]<>BLANK())) return IF('Table'[Actual Sales] = BLANK(),DIVIDE(MAX('Table'[Cumulative Sales]),max_days) * 'Table'[Cumulative Days in Month],'Table'[Cumulative Sales])
I attached my sample that you can download.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cmgovind ,
I created a calculated column to implement it that you can reference.
Forecast Sales = var max_days = CALCULATE(MAX('Table'[Cumulative Days in Month]),FILTER('Table','Table'[Actual Sales]<>BLANK())) return IF('Table'[Actual Sales] = BLANK(),DIVIDE(MAX('Table'[Cumulative Sales]),max_days) * 'Table'[Cumulative Days in Month],'Table'[Cumulative Sales])
I attached my sample that you can download.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This works great. Thanks for your help 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |