Filter date <= DatePicker.SelectedValue shifted by one day?

[SLMR]

Hello,

i have an dataverse table with a date (date only) Column. 

When i try to filter in an canvas app with two datepicker (to get teh range between the dates including the date (>= dp1 && <=) dp2)), everything works as expected for the lower threshold, but for the upper threhold it only shows "<" and not "<=".

i used timezone idepence in the table and local time in the datepicker.

Any idea what is wrong?

I could just ad a day to the datepicker, but i would like to understand what cases the problem

[VeGETzX]

Hi,

if your column is Date Only with Time-Zone-Independent behavior, Data stored in that column is DateTime with always 0.00 AM Time (UTC)

So, If user input 24/09/2023 it will be stored as '24/09/2023T00:00:00Z'

Then when try filter with DatePicker Local Time Zone, Power Apps will convert your time zone to UTC time zone,

e.g. I live in UTC+7 timezone, when filter date <= 24/09/2023 it will filter with <= 23/09/2023 5.00 PM and will cause rows that have date on 24/09/2023 will not in the result.

If your data is not require exact time and time zone, My suggestion is just change column behavior to 'Date Only' so your date will be just 'Date' without time value

Or in case that your table contains data and column behavior can't be changed without data loss, You can try modify your filter to look like this, by using the code, you will ensure that Power Apps will send filter request in date with 0.00 AM UTC time.

Filter(
    'PPF Playground Records',
    'Transaction Date' >= DateAdd(
        dpFtFrom.SelectedDate,
        TimeZoneOffset(dpFtFrom.SelectedDate) * -1,
        TimeUnit.Minutes
    ) And 'Transaction Date' <= DateAdd(
        dpFtTo.SelectedDate,
        TimeZoneOffset(dpFtTo.SelectedDate) * -1,
        TimeUnit.Minutes
    )
)

[LaurensM · Accepted solution]

Hi @SLMR,

I follow @VeGETzX's suggestion to change the column to a Date Only formatted Date column:

Solving the issue on a data layer is recommended over trying to solve the issue via code. You could also solve the issue with PowerFx code which I will include for informational purposes below.

To counteract the UTC conversion within your filter query, you could add your Time Zone Difference to the DatePicker.SelectedDate. Taking the example of the comment above, this would add 7 hours to our DatePicker date.

With(
    {
        wFilterDate1: DateAdd(
            dp1.SelectedDate,
            -TimeZoneOffset(),
            TimeUnit.Minutes
        ),
        wFilterDate2: DateAdd(
            dp2.SelectedDate,
            -TimeZoneOffset(),
            TimeUnit.Minutes
        )
    },
    Filter(
        Datasource,
        ColumnDate >= wFilterDate1 && ColumnDate <= wFilterDate2
    )
)

I hope this helps!

[SLMR]

I still have a question:

i used date  only 

But i changed from user local to time-zone indendent, because when i used used local time  it did not save the date that i wanted. (e.g when i used a date picker, to store the value in the db)

Now the date displayed in the dataverse table is the right value.

e.g 24.09.2023 (without any time information)

But comparing this value as <= 24.09.2023 from datepicker is not working.

For the datepicker the record will only be selected if i pick 25th with datepicker.

Because the database holds the right value I do not really understand the problem.

[VeGETzX · Accepted solution]

Dataverse table displayed in Power Apps Maker Studios will always show date without time information for a date column (even its was User local or Time Zone indepedent) but actual data is with time information.

You can view the raw, actual data stored in table via Dataverse REST API

GET https://{your_endpoint}.dynamics.com/api/data/v9.0/{your_table_entitysetname}

[SLMR]

Thanks for the answer. 

Now it works with the filter in the app, after setting the date column to user-local.

Anyway i dreictley get the next problem.

From the date i have to extract the month and the year in two additional columns:

like:

Text(Year(Datum),"##")

But this is not longer working after changing the column from time-zone independent to user local.

Is there no easy way to jeust store the date as it is picked or entered and work with it?

[SLMR]

Ok now i got it:

i have to set:

datetime->date only

and then in advanced setting again

date only

Thanks for your help. Have a nice weekend

[SLMR]

Hello,

i change the column to datetime->date only , advanced settings date only.

This works fine for the filter.

Bur now i get the problem, that the date picked from the datepicker is not stored in the right way in the database.

If i pick 01.09.2023 it stores 31.08.2023.

As i use caluclated columns to get the month Text(Month(DateColumn)) it now gives me the wrong month.

Is there an easy way to really just store the Date as it is selected?

I understand that the functions are helpfull for different time-zones, but i just need the date as it is picked.