Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 | ?? | ?? |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |