Forum Discussion
Using Date as VAR and Parameter Advanced Editor
Hi, I am trying to use the Var I have set for Startdate and Endate to set the parameter for my dataset from Parquet.
The value for StartofPeriodx = 1/01/2022 12:00:00 PM
The value for EndofPeriodx = 2022-02-08T23:59:59.9999999
I have currently used manual dates, how do I set the [Notification Date] >=StartDate and [Notification Date] <=EndDate
The start of my code is as follows:
Let
StartDate = StartOfPeriodx,
EndDate = EndOfPeriodx,
Source = Parquet.Document(File.Contents("\\notifs.pq")),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Notification date] >= #datetime(2022, 1, 1, 0, 0, 0) and [Notification date] <= #datetime(2022, 2, 2, 0, 0, 0))
Thanks for the help.
Anonymous wrote:
I have currently used manual dates, how do I set the [Notification Date] >=StartDate and [Notification Date] <=EndDate
As long as the variables are the same data type if should work exactly as you have it in your psuedo code.
eg.
Let StartDate = StartOfPeriodx, EndDate = EndOfPeriodx, Source = Parquet.Document(File.Contents("\\notifs.pq")), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Notification date] >= StartDate and [Notification date] <= EndDate)
4 Replies
Anonymous wrote:
I have currently used manual dates, how do I set the [Notification Date] >=StartDate and [Notification Date] <=EndDate
As long as the variables are the same data type if should work exactly as you have it in your psuedo code.
eg.
Let StartDate = StartOfPeriodx, EndDate = EndOfPeriodx, Source = Parquet.Document(File.Contents("\\notifs.pq")), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Notification date] >= StartDate and [Notification date] <= EndDate)Yeah I guess it is a date format issue then.
I believe it's only the EndDate giving me an error.
I got the following for the EndofPeriod
Source = if EndOfPeriod = null then Date.AddDays(Date.EndOfDay(DateTime.LocalNow()),-1) else EndOfPeriod
the above gives me a format of 2022-02-09T23:59:59.9999999, how can I change it to 1/01/2022 12:00:00 PM format?
Anonymous wrote:
Yeah I guess it is a date format issue then.
No, I was talking about Data Types (eg. Date vs DateTime vs DateTimeOffset), but it looks like you are using a DateTime here so that is probably not the issue. (although DateTime.LocalNow could be a problem depending on where you run your data refresh from. If you refresh on the Power BI Service the Date Time will be in UTC time, if you refresh on a Report Server the time will depend on the timezone setting on the server)
Anonymous wrote:
I believe it's only the EndDate giving me an error.
This is the first time you've mentioned seeing an error. What is the text of the error message?