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.
I have allocated planned budged over time proportionally up to end date and i have fact column and know i don't know how to allocate differenece of planned budget and actua from last transactionl up to end date. Help will be very appreciated!
@Sergun70can you provide soem sample data and expected result it will help for better support
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
My data looks like this: | ||
Project budget: | 12000 | |
Budget equally allocated | Actual | |
Jan | 1000 | 900 |
Feb | 1000 | 900 |
Mar | 1000 | 900 |
Apr | 1000 | 900 |
May | 1000 | 900 |
Jun | 1000 | 900 |
Jul | 1000 | 900 |
Aug | 1000 | |
Sep | 1000 | |
Oct | 1000 | |
Nov | 1000 | |
Dec | 1000 | |
Total | 12000 | 6300 |
Desired result: | ||
Month | Budget allocated | Actual |
Jan | 1000 | 900 |
Feb | 1000 | 900 |
Mar | 1000 | 900 |
Apr | 1000 | 900 |
May | 1000 | 900 |
Jun | 1000 | 900 |
Jul | 1000 | 900 |
Aug | 1000 | 1140 |
Sep | 1000 | 1140 |
Oct | 1000 | 1140 |
Nov | 1000 | 1140 |
Dec | 1000 | 1140 |
Total | 12000 | 12000 |
Hi @Sergun70,
If the [Month] column in dataset is sorted in correct order (Jan, Feb, Mar, Apr etc), please add an index column in Query Editor mode.
If the [Month] column is sorted in irregular order, (like, Jan, Mar, Nov, etc), please add a conditional column in Query Editor to list MonthNo for each Month name.
Then, in report view, create calculated column.
Actual 2 = IF ( 'Project budget'[Index] > MONTH ( TODAY () ), ( CALCULATE ( SUM ( 'Project budget'[Budget equally allocated] ), ALL ( 'Project budget' ) ) - CALCULATE ( SUM ( 'Project budget'[Actual] ), FILTER ( 'Project budget', 'Project budget'[Index] <= MONTH ( TODAY () ) ) ) ) / ( 12 - MONTH ( TODAY () ) ), 'Project budget'[Actual] )
To get your desired output, you can create a calculated table as well.
Result Table = SELECTCOLUMNS ( 'Project budget', "Month", 'Project budget'[Month], "Budget allocated", 'Project budget'[Budget equally allocated], "Actual", 'Project budget'[Actual 2] )
Best regards,
Yuliana Gu
Thanks a lot.
But this is not exactly what I want. I have separate date table and I have subheaders with data in month.
I want to allocate remainder of budget to future monthes where I haven't actual data.
Budget is allocated with measure:
DIVIDE (
CALCULATE ([GL_1.AMT];
FILTER (
'Headers_2';
'Headers_2'[Начало] <= MAX('Dates'[Date] )
&& 'Headers_2'[Окончание] >= MAX ('Dates'[Date])))
; (COUNTROWS (
DATESBETWEEN (
'Dates'[Date]
; FIRSTDATE('Headers_2'[Начало])
; LASTDATE (Headers_2[Окончание] )
)))
)
Budget | Actual | |
jan | ||
mat | 618 078,43 | 2 473 359,46 |
pcs | 1 579 185,22 | 64 03 622,48 |
etc | 283 910,50 | 39 420,71 |
prof | 621 912,38 | |
exp | 6 11,24 | |
labour | 1 071 360,39 | 406 201,90 |
feb | ||
mat | 558 264,38 | |
pcs | 1 426 60,84 | 3 2 660,08 |
etc | 26 435,29 | 449 22,88 |
prof | 561 77,31 | |
exp | 5 546,93 | |
labour | 967 680,35 | 1 630 730,48 |
mar
......
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |