Calculated table

hi ChiRomeu ,

 

I used power query for this. 

 

create a blank query, copy and paste the below code into the advanced editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTI0MjAwANIGhvpABBaM1QHLGgFFjUzQZI2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Amount = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Amount", type number}, {"Date", type date}}),
    //create a list of months
    Month = Table.AddColumn(#"Changed Type", "Month", each {1..12} ),
    //expand the months
    #"Expanded Month" = Table.ExpandListColumn(Month, "Month"),
    //create a date using year and month
    #"Added Custom" = Table.AddColumn(#"Expanded Month", "Date_", each #date([Year],[Month],01)),
    //transform to look like output
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date_", type date}, {"Month", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date_", "Date"}})
in
    #"Renamed Columns"

 

let me know if this works for you.

 

thanks.

Hi ChiRomeu ,

Kindly add a column after the month lost step.

Use [amount] / List.Count([Month] in the step and then expand the month list.