List all months between two dates of a contract in a seperate table

[Ctwzl]

I really need your help because I am stuck for quite some time now to generate the revenue by month of contracts. I have a contracts table with a start and end date and I want to calculate the revenue per month. I have tried many solutions and used ChatGPT but I keep failing to get the desired output. I think the main reason is that many solutions will list all the days between the start and end date. Then create a calculated table with crossjoin and that's too much data (my assumption) because it loads until I get an error message that something went wrong.

In order to fix this I guess I only need the months between the dates to limit the data. If a contract runs from the 1st of January to 31st of March I only want three rows and not 90 rows for all days (February should always be 28 days).

My contracts table looks like this:

Contract ID	IsDuplicate	Start Date	End Date	Annual Value
A	1	01-01-2024	31-03-2024	1200
A	0	01-01-2024	31-03-2024	1200
B	1	12-04-2024	12-06-2024	2400

It's worth mentioning here that my contracts table has duplicates that I want to remove by selecting IsDuplicate = 1 to avoid double revenue due to those contract lines.

My expected output of a calculatedtable would be:

Contract ID	Year-Month	DaysInMonth	ContractDays in Month	Annual Value
A	2024-01	31	31	1200
A	2024-02	28	28	1200
A	2024-03	31	31	1200
B	2024-04	30	18	2400
B	2024-05	31	31	2400
B	2024-06	30	12	2400

I would really appreciate some help here to get this working. If you think there is a better method for this than please let me know.

[lbendlin · Accepted solution]

Sigh...

[lbendlin]

This is a standard amortization pattern. but you will need to define what to do with partial months like your contract B.

(February should always be 28 days)

Nopedy-nope.  This year it was 29.  But it wouldn't really matter if you amortize monthly with each month getting 1/12th of the yearly value.  Which takes us right back to your contract B.  

For 2024 you can multiply the annual value by the contract days and divide by 366. For other years use 365 etc.  This will become fun when contracts extend across years.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYgNDXSAyMjAyAXKMgRxjGMfQyMBAKVYHotSAOKVOUFMNjXQNTBCyugZmMI6RCUhpLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, IsDuplicate = _t, #"Start Date" = _t, #"End Date" = _t, #"Annual Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IsDuplicate", Int64.Type}, {"Annual Value", Currency.Type}, {"Start Date", type date}, {"End Date", type date}},"nl"),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([IsDuplicate] = 1)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Date", each List.Dates([Start Date],Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0))),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Date", "Year-Month", each Date.ToText([Date],[Format = "yyyy-MM"]),type text),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Contract ID", "Year-Month"}, {{"Contract Days in Month", each Table.RowCount(_), Int64.Type}, {"Annual Value", each List.Max([Annual Value]), type nullable number}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[Ctwzl]

I know that this year February had 29 days. However for some reason the annual value (this data comes from a different report) is always calcualted based on 365 days no matter the year. Because I want to make sure the revenue by month matches I need use 28 days for February. Otherwise I will always slightly off for everything in 2024.

[ThxAlot · Accepted solution]

[lbendlin · Accepted solution]

Sigh...