Forum Discussion

speedramps's avatar
speedramps
Super User
6 years ago
Solved

Power BI calendar batch date

I want to pass a ETL batch date from my data warehouse to my Power BI calendar.
I know how to hard code a Power BI calendar, but how can I replace the hardcoded CurrentDate = #date(2020, 1, 1)
with a dynamic value from a file ?
See the line in red, and the screen print of the file.

Many thanks. 

 

let

    /*

    *** Create Calendar ***

    */

    #"==Calandar Parameters==" = 1,

    StartDate = #date(2019, 1, 1),

    EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())), 

    //Current date for calculating the offset. Hard coded to 01/01/2020

    CurrentDate = #date(2020, 1, 1),

     //List dates between the start and end date

    #"==Build Date Columns==" = CurrentDate,

    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

    // Add Human-Friendly Columns

    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),

    #"==Add Calendar Columns==" = #"Changed Type to Date",

    #"Added Calendar Year" = Table.AddColumn(#"==Add Calendar Columns==", "Year", each Date.Year([Date]), Int64.Type),

    #"Added Quatrer Offset" = Table.AddColumn(#"Added Calendar Year", "QuarterOffset", each (Date.Year([Date]) - Date.Year(CurrentDate) ) * 4

+ Date.QuarterOfYear([Date]) - Date.QuarterOfYear(CurrentDate), Int64.Type),

    // // This calculates the month offset

    #"Added Month Offset" = Table.AddColumn(#"Added Quatrer Offset", "MonthOffset", each (Date.Year([Date]) - Date.Year(CurrentDate) ) * 12

+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type)

in

    #"Added Month Offset"




 

Need Help
  • hnguy71's avatar
    hnguy71
    6 years ago

    speedramps ,

    I would create a new blank query and call it something like: param_CurrentDate
    From there you can have in your code something like this:

    let
    // Substitute Table[Date] to your query table you'd like to retrieve from and the column in which holds the current date
    CurrentDate = List.Max(Table[Date])
in
    CurrentDate


    And, finally, you can call param_CurrentDate from your calendar query.

     

2 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    6 years ago

    speedramps ,

    I would create a new blank query and call it something like: param_CurrentDate
    From there you can have in your code something like this:

    let
    // Substitute Table[Date] to your query table you'd like to retrieve from and the column in which holds the current date
    CurrentDate = List.Max(Table[Date])
in
    CurrentDate


    And, finally, you can call param_CurrentDate from your calendar query.