Using Date as VAR and Parameter Advanced Editor

[Anonymous]

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.

[d_gosbell · Accepted solution]

---

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

[Anonymous]

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?

[d_gosbell]

---

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

[Anonymous]

Cheers mate, The previous way worked. It kept getting timed out as the date range was over a year.