Create new rows with future review dates (M or DAX)

[CLEbi]

I have a table with customer ID, other details, Next Review Date, and Review Term (months). I want to generate new rows representing each customer's future review dates for the next rolling 24 months. I want all customer detail fields to be the same as the original row, but I want the Next Review Date to update accordingly. I plan to use this data to show count of anticipated customer reviews by month for the next 2 years.

See example below and let me know if there is a better solution. Thank you!

Current table

Customer ID	Other details	Next Review Date	Review Term (months)
123456	XYZ	1/16/25	6

New table

Customer ID	Other details	Next Review Date	Review Term (months)
123456	XYZ	1/16/25	6
123456	XYZ	7/16/25	6
123456	XYZ	1/16/26	6
123456	XYZ	7/16/26	6

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRioiMApKG+oZm+kamQJaZUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Other details" = _t, #"Next Review Date" = _t, #"Review Term (months)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Next Review Date", type date}, {"Review Term (months)", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #table({"multiplier"},{{0},{1},{2},{3}})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"multiplier"}, {"multiplier"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [Next Review Date],each Date.AddMonths([Next Review Date],6*[multiplier]),Replacer.ReplaceValue,{"Next Review Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Next Review Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"multiplier"})
in
    #"Removed Columns"

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.

[govind_021 · Accepted solution]

Hey you can try this approach :
using m
1) Duplicate your table : Right-click on the query and duplicate it to work on a copy.
2) Add a Custom Column for Future Dates: Go to Add Column > Custom Column.
3) Use this formula : 
List.Generate(
() => [Next Review Date],
each _ <= Date.AddMonths([Next Review Date], 24),
each Date.AddMonths(_, [Review Term])
)
4) Click the expand icon on the new column to turn the list into rows.This will duplicate the original rows and append each future review date.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRioiMApKG+oZm+kamQJaZUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Other details" = _t, #"Next Review Date" = _t, #"Review Term (months)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Next Review Date", type date}, {"Review Term (months)", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #table({"multiplier"},{{0},{1},{2},{3}})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"multiplier"}, {"multiplier"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [Next Review Date],each Date.AddMonths([Next Review Date],6*[multiplier]),Replacer.ReplaceValue,{"Next Review Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Next Review Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"multiplier"})
in
    #"Removed Columns"

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.