Forum Discussion

Cris_Cuevas_'s avatar
Cris_Cuevas_
Frequent Visitor
4 years ago

Date filtering error

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

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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_'s avatar
      Cris_Cuevas_
      Frequent Visitor
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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