Forum Discussion
Dynamically calculate a calendar maintenance table based on a start date
Here is an example of the one for Months. If it works, you could adapt the approach for weeks and quarters. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. This could easily be converted into a function where you could just input the desired start date.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Date = #"Changed Type"{0}[Date],
Custom1 = List.Transform({0..Number.RoundUp(Duration.Days(Date.EndOfYear(Date)-Date)/30)-2}, each Date.AddMonths(Date, _)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "RefreshDates"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"RefreshDates", type date}})
in
#"Changed Type1"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thank you for the assistance on this. This is similar to what I need, but how do I incorporate it in a table with my data. I'dd like to add the future dates in the columns next to each report row. Is it possible to build a relationship witht he results?
| Type | Frequency | Start Date | Refresh 1 | Refresh 2 | Refresh 3 |
| Report 1 | Monthly | 02/16/20 | 03/16/20 | ?? | ?? |
| Report 2 | Quarterly | 01/01/20 | 03/01/20 | ?? | ?? |
| Report 3 | Annually | 06/01/20 | 06/01/21 | ?? | ?? |