Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
speedramps
Super User
Super User

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"


Untitled.png

 

1 ACCEPTED SOLUTION
hnguy71
Memorable Member
Memorable Member

@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.

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

2 REPLIES 2
hnguy71
Memorable Member
Memorable Member

@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.

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Thank yoi hnguy71.  Yes that is!  

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.