Forum Discussion
Helper IDistribute budget evenly between startdate and finishdate
I have a table that looks like the one below.
I have been given the task to find a way to evenly distribute the budget between startdate and enddate in a visual. Probably something like a barchart that will show the budget per year and month by project.
Does anyone know how to solve this?
| id | startdate | enddate | budget |
| 1 | 01.01.2018 | 01.01.2023 | 74111 |
| 2 | 02.01.2019 | 02.01.2020 | 48705 |
| 3 | 03.01.2018 | 03.01.2024 | 49945 |
| 4 | 04.01.2029 | 04.01.2031 | 76701 |
| 5 | 05.01.2018 | 05.01.2019 | 49422 |
| 6 | 06.01.2025 | 06.10.2025 | 73366 |
| 7 | 07.01.2022 | 07.01.2022 | 93599 |
| 8 | 08.01.2018 | 08.01.2021 | 76321 |
| 9 | 09.01.2018 | 09.01.2024 | 118738 |
Thank you so much!
Hi pbi_taken
This would be a simple feat with Power Query and I prefer this method. Please you need to count how many months are within the range if you need the granularity to be by month then you generate rows with months/dates within the range. This is a sample M Code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY9LCgQxCAXv4roZ/MZ4ltD3v8ZEkwEZyKZ4FX2uBQQPIH32Y6TZgGWDKxHB+yzgjPh60YBxg05HKy9/ofR5F1jTi9DjJaHeKBpINvLhePZaRtbnWSuhoczljYzGnWcHCH/gImOU5xn59fgfQiyivFo1+94LfPoJn35VPboX7V6i6TLhfb8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, startdate = _t, enddate = _t, budget = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"startdate", type date}, {"enddate", type date}, {"budget", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Count of Months", each Number.Round(Number.From(([enddate] - [startdate])/( 365.25 / 12 )) ,0 )), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Split", each {0..[Count of Months]}), #"Expanded Split" = Table.ExpandListColumn(#"Added Custom1", "Split"), #"Added Custom2" = Table.AddColumn(#"Expanded Split", "Dates", each Date.AddMonths([startdate],[Split])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Distributed Budget", each [budget]/( [Count of Months] + 1) ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Count of Months", "Split"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Dates", type date}, {"Distributed Budget", type number}}) in #"Changed Type1"The total of distributed budget should be equal to the total of the actual budget per id
5 Replies
- Sahir_Maharaj
Super User
Hello pbi_taken,
Can you please try this approach:
1. Create a Date Table
DateTable = ADDCOLUMNS( CALENDAR(MIN(Table[startdate]), MAX(Table[enddate])), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "YearMonth", YEAR([Date]) * 100 + MONTH([Date]), "MonthName", FORMAT([Date], "MMM") )2. Calculate the Number of Months Between Start and End Date
MonthsBetween = DATEDIFF(Table[startdate], Table[enddate], MONTH) + 13. Calculate the Monthly Budget
MonthlyBudget = DIVIDE(Table[budget], Table[MonthsBetween])4. Sum the Monthly Budget
DistributedBudget = VAR StartDate = MIN(Table[startdate]) VAR EndDate = MAX(Table[enddate]) VAR MonthlyBudget = DIVIDE(SUM(Table[budget]), DATEDIFF(StartDate, EndDate, MONTH) + 1) RETURN CALCULATE( MonthlyBudget, FILTER( DateTable, DateTable[Date] >= StartDate && DateTable[Date] <= EndDate ) )Hope this helps.
pbi_takenThis looks promosing, but what should the relation between datetable and table be?
If its between startdate and datetable it filters for just the startdate and if it's no relationship every year is in my graph and start and enddate does not matter.. like the image below, when actually startdate is in january 2018 and enddate is in january 2024...Hi pbi_taken ,
There is no need to create a relationship between the two tables. Just put the id field in the legend. Also, you can put startdate and enddate in tooltip if you want to know their exact dates.
More detailed information can be found in the attachment.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- danextian
Hi pbi_taken
This would be a simple feat with Power Query and I prefer this method. Please you need to count how many months are within the range if you need the granularity to be by month then you generate rows with months/dates within the range. This is a sample M Code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY9LCgQxCAXv4roZ/MZ4ltD3v8ZEkwEZyKZ4FX2uBQQPIH32Y6TZgGWDKxHB+yzgjPh60YBxg05HKy9/ofR5F1jTi9DjJaHeKBpINvLhePZaRtbnWSuhoczljYzGnWcHCH/gImOU5xn59fgfQiyivFo1+94LfPoJn35VPboX7V6i6TLhfb8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, startdate = _t, enddate = _t, budget = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"startdate", type date}, {"enddate", type date}, {"budget", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Count of Months", each Number.Round(Number.From(([enddate] - [startdate])/( 365.25 / 12 )) ,0 )), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Split", each {0..[Count of Months]}), #"Expanded Split" = Table.ExpandListColumn(#"Added Custom1", "Split"), #"Added Custom2" = Table.AddColumn(#"Expanded Split", "Dates", each Date.AddMonths([startdate],[Split])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Distributed Budget", each [budget]/( [Count of Months] + 1) ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Count of Months", "Split"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Dates", type date}, {"Distributed Budget", type number}}) in #"Changed Type1"The total of distributed budget should be equal to the total of the actual budget per id
