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.
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!
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |