Date filtering error

[Cris_Cuevas_]

Hi,

I have an error with the date filter for daylight saving time.

If I put as start of the filter the day of the daylight saving time change (September 5) I get that filter generated by the DAX query internally, with Performance Analyzer, and this include 1 extra hour. But it does not show me the information for September 5 in any table that is related to the calendar, only the one for day 6.

  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('DT'[shiftdate])),
      AND(
        'DT'[shiftdate] >= (DATE(2021, 9, 5) + TIME(1, 0, 0)),
        'DT'[shiftdate] < DATE(2021, 9, 6)
      )
    )

In the image is the filter and a table showing the same field used for the filter.

But if I do it for the day before, the filter changes as seen in the code below and the tables show me information from day 4 to day 6.

  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Calendario'[Date])),
      AND(
	    'Calendario'[Date] >= DATE(2021, 9, 4), 
		'Calendario'[Date] < DATE(2021, 9, 6))
    )

 How can I fix this error ☹️ ?

Thanks

[amitchandak]

@Cris_Cuevas_ , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

try like

VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('DT'[shiftdate])),
      AND(
        'DT'[shiftdate]+ TIME(1, 0, 0) >= DATE(2021, 9, 5) ,
        'DT'[shiftdate] < DATE(2021, 9, 6)
      )
    )

[Cris_Cuevas_]

Hello @amitchandak ,

Here is an example with the calendar only and the same problem.

https://1drv.ms/u/s!AoL7wm4LlTOIhfo8eyfdpyd9jgw--Q?e=Frnhy4

Where the DAX Query to generate the table is this:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Calendar'[Date])),
      AND(
        'Calendar'[Date] >= (DATE(2021, 9, 5) + TIME(1, 0, 0)),
        'Calendar'[Date] < DATE(2021, 9, 8)
      )
    )

  VAR __DS0Core = 
    CALCULATETABLE(
      DISTINCT('Calendar'[Date]),
      KEEPFILTERS(__DS0FilterTable)
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, 'Calendar'[Date], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Calendar'[Date]

[Anonymous]

HI @Cris_Cuevas_,

I test on your sample file but can't reproduce the issue. The filter works well on my side. 

What version of power bi desktop are you worked on? Did you use the common power bi desktop or the report server optimized version? Please share more detailed information to help us clarify your scenario.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Cris_Cuevas_]

Hi @Anonymous ,

I'm using Power BI Desktop Version: 2.97.861.0 64-bit (september 2021).

In Chile we had a time change on September 5th, we moved the clocks forward 1 hour.
With this the DAX query that Power BI generates internally (I obtained this from Performance Analyzer) gives me the date filter but with 1 hour added which makes my filter does not behave as I expect.
If I put it to start on the 5th it will show me the dates from the 6th onwards.
The problem is only with this particular day for that condition, but I don't know how to fix it.

Thanks

[Anonymous]

HI @Cris_Cuevas_,

Perhaps you can take a look at below blog about handle daylight saving and timezone issue if helps for your scenario:

Chris Webb's BI Blog: Daylight Saving Time And Time Zones In M Chris Webb's BI Blog (crossjoin.co.uk...

Regards,
Xiaoxin Sheng