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 hope you are doing well!
I have a question about budgeting issue, I need your help. Thanks for your support in advance!
I have three columns, Start Date, End Data and Amount of the budget.
I have to distribute the amount to years based on the contract duration (months and year(s)). For example, I have a contract start 1/8/2019, end 1/1/2023, amount is 302500, so I need to divide the amount to three years and five months. So the budget of 2019 well be 36890, and each years the budget amount is 88537.
A sample data in the follwoing file (URL).
https://www.dropbox.com/s/trynlxzkhcs0tnw/Budget%20Distribution.pbix?dl=0
Is this possible in Power BI?
Thanks for your support in advance!
Best Regards
Mahmoud
Solved! Go to Solution.
Hi @aboshabo ,
You can try to use following measure formula to calculate each year budget based on current ContractN, year.
Measure = VAR maxdate = MAX ( 'Table'[Date] ) VAR summary = SUMMARIZE ( ContractReportExcel, [ContractN], [Amount], [Contract Start Date], [Contract End Date], "duration", DATEDIFF ( [Contract Start Date], [Contract End Date], MONTH ) ) VAR unit = SUMX ( FILTER ( summary, maxdate IN CALENDAR ( [Contract Start Date], [Contract End Date] ) && [ContractN] IN VALUES ( ContractReportExcel[ContractN] ) ), [Amount] / [duration] ) VAR _calendar = ADDCOLUMNS ( CALENDAR ( CALCULATE ( MIN ( ContractReportExcel[Contract Start Date] ), ALLSELECTED ( ContractReportExcel ), VALUES ( ContractReportExcel[ContractN] ) ), CALCULATE ( MAX ( ContractReportExcel[Contract End Date] ), ALLSELECTED ( ContractReportExcel ), VALUES ( ContractReportExcel[ContractN] ) ) ), "Month", MONTH ( [Date] ) ) VAR _duration = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( _calendar, YEAR ( [Date] ) = YEAR ( maxdate ) ), "M", [Month] ) ) ) RETURN unit * _duration
Notice: Table is a calendar I created for calculate, it no has relationship to original table.
I also attached sample file below.
Regards,
Xiaoxin Sheng
Hi @aboshabo ,
You can try to use following measure formula to calculate each year budget based on current ContractN, year.
Measure = VAR maxdate = MAX ( 'Table'[Date] ) VAR summary = SUMMARIZE ( ContractReportExcel, [ContractN], [Amount], [Contract Start Date], [Contract End Date], "duration", DATEDIFF ( [Contract Start Date], [Contract End Date], MONTH ) ) VAR unit = SUMX ( FILTER ( summary, maxdate IN CALENDAR ( [Contract Start Date], [Contract End Date] ) && [ContractN] IN VALUES ( ContractReportExcel[ContractN] ) ), [Amount] / [duration] ) VAR _calendar = ADDCOLUMNS ( CALENDAR ( CALCULATE ( MIN ( ContractReportExcel[Contract Start Date] ), ALLSELECTED ( ContractReportExcel ), VALUES ( ContractReportExcel[ContractN] ) ), CALCULATE ( MAX ( ContractReportExcel[Contract End Date] ), ALLSELECTED ( ContractReportExcel ), VALUES ( ContractReportExcel[ContractN] ) ) ), "Month", MONTH ( [Date] ) ) VAR _duration = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( _calendar, YEAR ( [Date] ) = YEAR ( maxdate ) ), "M", [Month] ) ) ) RETURN unit * _duration
Notice: Table is a calendar I created for calculate, it no has relationship to original table.
I also attached sample file below.
Regards,
Xiaoxin Sheng
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |