Relative Date filter incorrectly filtering on yesterday's date

[shinney]

Hello, I'm trying to set up a report where it's updated daily and filter to yesterday's date. However, when I set up the Relative date filter to "is in the last 1 day", I am still getting today's date (see photo below). I am in UTC -5 timezone (Eastern Time), but even then, the last 1 day should display April 18 - April 18, instead of April 19 - April 19. If I choose to filter to "is in the last 2 days", the report will show data from April 18 - April 19, which is 2 days and hence will report incorrectly.



Any ideas why this is the case? Is there a setting I need to toggle somewhere?

Thanks

[Anonymous · Accepted solution]

Hi @shinney ,

Thanks for the reply from Bipin-Lala .

The problem when your local time zone is so far away from the UTC time zone is that today's date is usually not really today, it could be yesterday or tomorrow depending on where you live.

You can get the local time zone from the Web API using a web query like in the Solving DAX Time Zone Issue in Power BI - RADACAD article.

Getting the correct local date and time is part of the solution, since it still doesn't work if you use a relative date slicer.

The second part of the solution is to create a relative date slicing and dicing experience using the offset date slicer method described in the All in One: Script to Create Date Dimension in Power BI using Power Query - RADACAD article.

Offset date slicers are a set of slicers that can be used on certain offset columns of dates, months, quarters, and years in a calendar or date table.

This means that for this method you need to have a custom Date table.

The trick is that when creating the date table you should consider Today from the query generated via WebAPI:

let
    LocalToday=()=>
        let 
            Source = Xml.Document(Web.Contents("http://api.timezonedb.com/v2/get-time-zone?key=XYZ&format=xml&by=zone&zone=NZST")),
            Value = Source{0}[Value],
            Value1 = Value{12}[Value]
        in
            Value1,
    // configurations start
    Today=Date.From(DateTime.From(LocalToday())), // today's date
    FromYear = 2018, // set the start year of the date dimension. dates start from 1st of January of this year
    ToYear=2021, // set the end year of the date dimension. dates end at 31st of December of this year
    StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
    firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day, Sunday....
    // configuration end
    FromDate=#date(FromYear,1,1),
    ToDate=#date(ToYear,12,31),
    Source=List.Dates(
        FromDate,
        Duration.Days(ToDate-FromDate)+1,
        #duration(1,0,0,0)
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
    FiscalMonthBaseIndex=13-StartofFiscalYear,
    adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
    #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
    #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
    #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
    #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
    #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date]-Today, type duration),
    #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days",{{"Age", "Day Offset"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Month Offset", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year Offset", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)
+([Quarter]-Date.QuarterOfYear(Today))),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Quarter Offset", Int64.Type}})
in
    #"Changed Type4"

Set this table to "Mark as Date Table" and you're good to go. You can create an offset date slicer column and use Power BI reports in the Power BI service to process local dates and times.

Just copy and paste the code into a blank query in Power Query and change the time zone.

If you have any other questions, please see this blog:

Relative Date Slicer for Your Local Time Zone in Power BI - RADACAD

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Bipin-Lala]

Hi @shinney,

Filtering data specifically for yesterday, using a relative slicer might not be feasible because the filter logic is based on the last 24 hours, not on calendar days like "yesterday."

This means that if it's currently April 19th, "in the last 1 day" includes data from 24 hours ago until now, which can span across two calendar days.

Solution

To filter data for yesterday, you can create a custom column or measure that dynamically calculates whether a date is yesterday based on the current date. This custom column or measure would check if each date corresponds to yesterday's date and assign a flag accordingly.

For Example: You can create a calculate column using the following DAX

IsYesterday = IF('Table'[Date].[Date] = TODAY() - 1, TRUE(), FALSE())

 The IF statement checks if each date matches yesterday's date and returns TRUE or FALSE accordingly.

And now you just need to filter your visuals where "IsYesterday" is TRUE 😄

Hope this helps! Let me know if you have any questions.

[shinney]

Hello! Thanks for the response. I understand how the filters work now.

The custom column option would not be ideal as I have multiple visuals with dates, and they would all need to be changed, as well as having a custom filter instead of using a default filter will not make it as user friendly.

Instead, since you mentioned that Last 1 Day filter actually means last 24 hours ( which in my opinion, needs to change ... there is a seperate Relative Time filter, so this makes no sense), I just need to change my dates instead of 00:00:00, will be 11:59:59, which in theory should allow this to work?