Distribute budget evenly between startdate and finishdate

[pbi_taken]

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!

[danextian · Accepted solution]

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

[Sahir_Maharaj]

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) + 1

3. 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_taken]

This 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...

[Anonymous]

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.

[pbi_taken]

Thanks! but i still see issues with this as it shows me the same amount for all years. It should se how many months is in the start year and end year and evenly distribute by number of months in the year..