Splitting period data into months

Hi lkarolak,

In your scenario, you can create a function use a blank query like below:

(startdate as date, enddate as date) as table =>
let
 //startdate=#date(2015,8,1),
 //enddate=#date(2016,1,31),
 Source = {Number.From(startdate)..Number.From(enddate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Calculated End of Month" = Table.TransformColumns(#"Renamed Columns",{{"Date", Date.EndOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month"),
    #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Original", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 Then rename the function as fnGetAllocationBase.

In the query which get the fact table, invoke this function like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDTXNTTQRbCNdI1BHEsDpVidaCUjiLiZrqEusnKwEkMDA6CiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, BEGIN = _t, END = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"BEGIN", type date}, {"END", type date}, {"VALUE", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnGetAllocationBase([BEGIN],[END])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BEGIN", "END"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Original", "Months_Total"}, {"Custom.Original", "Custom.Months_Total"}),
    #"Expanded Custom.Original" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Original", {"Date", "EndOfYear"}, {"Custom.Original.Date", "Custom.Original.EndOfYear"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom.Original", "Value_update", each [VALUE]/[Custom.Months_Total]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.Original.Date", type date}, {"Custom.Original.EndOfYear", type date}})
in
    #"Changed Type1"

Reference:

Allocate Units Based on Dates Using Power Query

Best Regards,
Qiuyun Yu