Forum Discussion

JQuon's avatar
JQuon
Helper I
6 years ago

Dynamically calculate a calendar maintenance table based on a start date

Power BI experts...each report we create has a different refresh schedule (Weekly, Monthly, Qtrly). Is there a way to create a calculated table or calendar that will show all the refresh dates in a year based on "x" date?
For example If I set a baseline date of a report for January 1st that needs to be refreshed monthly, I would like a table that dynamically shows that a refresh is required every 1st of the month for the rest of the year or until x date. Another example is if the start date is February 5th and the frequency is quarters, would like the next quarter refresh to reflect July April 5th.
 
Is this possible?
Need Help

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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

    • JQuon's avatar
      JQuon
      Helper I
      6 years ago

      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?

       

      TypeFrequencyStart DateRefresh  1Refresh 2 Refresh 3
      Report 1Monthly02/16/2003/16/20????
      Report 2Quarterly01/01/2003/01/20????
      Report 3Annually06/01/2006/01/21????