topic Re: Using Date as VAR and Parameter Advanced Editor in Report Server

Using Date as VAR and Parameter Advanced Editor

Anonymous — Wed, 09 Feb 2022 04:47:29 GMT

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.

Re: Using Date as VAR and Parameter Advanced Editor

d_gosbell — Wed, 09 Feb 2022 23:11:56 GMT

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)

Re: Using Date as VAR and Parameter Advanced Editor

Anonymous — Thu, 10 Feb 2022 00:41:15 GMT

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?

Re: Using Date as VAR and Parameter Advanced Editor

d_gosbell — Thu, 10 Feb 2022 02:53:57 GMT

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?

Re: Using Date as VAR and Parameter Advanced Editor

Anonymous — Fri, 11 Feb 2022 02:04:26 GMT

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