Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Solved! Go to Solution.
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.
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.
Thank yoi hnguy71. Yes that is!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |