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 DA...
Need Help
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

    • Cris_Cuevas_'s avatar
      Cris_Cuevas_
      Frequent Visitor
      4 years ago

      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