How to priodize a payment in Power BI

Hi Anonymous 

 

Here is my solution. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcnBCQAgDEPRXXIWaaN2mdL91zCiGHL5vEw4Gsy7TuNUuGmoluCjOLQU/DRUw7rzUCjmpdo=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Income date" = _t, Income = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Income date", type date}, {"Income", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Income Quarter", each List.Generate(
    () => [x = 1, y = [Income date]], 
    each [x] <= 4,
    each [x = [x] + 1, y = Date.AddQuarters([y], 1)],
    each [y] 
)),
    #"Expanded Income Quarter" = Table.ExpandListColumn(#"Added Custom", "Income Quarter"),
    Custom1 = Table.TransformColumns(#"Expanded Income Quarter", {{"Income Quarter", each "Q" & Text.From(Date.QuarterOfYear(_)) & " " & Text.From(Date.Year(_))}}),
    #"Added Custom1" = Table.AddColumn(Custom1, "Quarterly Income", each [Income]/4),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Project", "Income Quarter", "Quarterly Income"})
in
    #"Removed Other Columns"

Steps are:

1. Create a custom column to have a list of 4 dates on every row. 

2. Expand the lists to new rows. 

3. Add a custom step to transform the dates to corresponding quarter year values. 

4. Add a custom column to divide income values by 4. 

5. Remove unnecessary columns.  

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.