Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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)
)
)
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]
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
Hi @v-shex-msft ,
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
HI @Cris_Cuevas_,
Perhaps you can take a look at below blog about handle daylight saving and timezone issue if helps for your scenario:
Regards,
Xiaoxin Sheng