Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all, I would like to request assistance to get the annual budget split over months with seasonality. So, what I have is the prior 3 Years actual figures with different Items (only one Year illustrated below). These I would like to split for the new budget year as an average of the prior 3 years trends. Here is a sample of the prior year’s actuals per item:
Period | Item | Actuals | % |
201607 | 111 | 24751 | 7.5% |
201608 | 111 | 25778 | 7.8% |
201609 | 111 | 25688 | 7.8% |
201610 | 111 | 25968 | 7.9% |
201611 | 111 | 25577 | 7.8% |
201612 | 111 | 25496 | 7.8% |
201701 | 111 | 27467 | 8.4% |
201702 | 111 | 28947 | 8.8% |
201703 | 111 | 29095 | 8.9% |
201704 | 111 | 27812 | 8.5% |
201705 | 111 | 29078 | 8.9% |
201706 | 111 | 32726 | 10.0% |
201607 | 125 | 24412 | 7.3% |
201608 | 125 | 25425 | 7.6% |
201609 | 125 | 25336 | 7.6% |
201610 | 125 | 29454 | 8.8% |
201611 | 125 | 29011 | 8.7% |
201612 | 125 | 25650 | 7.7% |
201701 | 125 | 27632 | 8.3% |
201702 | 125 | 29122 | 8.7% |
201703 | 125 | 29270 | 8.7% |
201704 | 125 | 27980 | 8.4% |
201705 | 125 | 29253 | 8.7% |
201706 | 125 | 32277 | 9.6% |
201607 | 131 | 495 | 7.5% |
201608 | 131 | 516 | 7.8% |
201609 | 131 | 514 | 7.8% |
201610 | 131 | 519 | 7.9% |
201611 | 131 | 512 | 7.8% |
201612 | 131 | 510 | 7.8% |
201701 | 131 | 549 | 8.4% |
201702 | 131 | 579 | 8.8% |
201703 | 131 | 582 | 8.9% |
201704 | 131 | 556 | 8.5% |
201705 | 131 | 582 | 8.9% |
201706 | 131 | 655 | 10.0% |
Then I have a sample of the Budget for the next financial year as one value only for the year. This I would like to split as per the performance of the average of the prior years, e.g.
Budget Year | Item | Budget |
2017/2018 | 111 | 394058 |
2017/2018 | 125 | 411829 |
2017/2018 | 131 | 8538 |
So, the desired outcome in Power BI automatically adjusting would be calculating the seasonality or monthly trend from prior years multiplied by the annual budget figure to illustrate the monthly budget e.g.
Period | Item | Seasonality | Monthly Budget |
201707 | 111 | 7.5% | 29,701 |
201708 | 111 | 7.8% | 30,933 |
201709 | 111 | 7.8% | 30,825 |
201710 | 111 | 7.9% | 31,161 |
201711 | 111 | 7.8% | 30,692 |
201712 | 111 | 7.8% | 30,595 |
201801 | 111 | 8.4% | 32,960 |
201802 | 111 | 8.8% | 34,737 |
201803 | 111 | 8.9% | 34,913 |
201804 | 111 | 8.5% | 33,375 |
201805 | 111 | 8.9% | 34,893 |
201806 | 111 | 10.0% | 39,271 |
201707 | 125 | 7.3% | 30,027 |
201708 | 125 | 7.6% | 31,272 |
201709 | 125 | 7.6% | 31,163 |
201710 | 125 | 8.8% | 36,228 |
201711 | 125 | 8.7% | 35,683 |
201712 | 125 | 7.7% | 31,549 |
201801 | 125 | 8.3% | 33,988 |
201802 | 125 | 8.7% | 35,820 |
201803 | 125 | 8.7% | 36,002 |
201804 | 125 | 8.4% | 34,415 |
201805 | 125 | 8.7% | 35,981 |
201806 | 125 | 9.6% | 39,701 |
201707 | 131 | 7.5% | 644 |
201708 | 131 | 7.8% | 670 |
201709 | 131 | 7.8% | 668 |
201710 | 131 | 7.9% | 675 |
201711 | 131 | 7.8% | 665 |
201712 | 131 | 7.8% | 663 |
201801 | 131 | 8.4% | 714 |
201802 | 131 | 8.8% | 753 |
201803 | 131 | 8.9% | 756 |
201804 | 131 | 8.5% | 723 |
201805 | 131 | 8.9% | 756 |
201806 | 131 | 10.0% | 851 |
Your assistance in getting these calculations done in Power BI would be truly appreciated.
You may use LOOKUPVALUE Function to add a calculated column or refer to this example.
@v-chuncz-msft the LOOKUPVALUE will not work as the annual figures first need to be split monthly according to the distribution.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |