Slicer returning results outside date range

[DonIncognito]

I have a date slicer hooked up to a date table. The slicer has two filters, one for anything after 1/1/2020, and one for choosing the end date using the date offset calculation.

If I have the beginning date selected, it feels like the slicer is returning all the results in the date table.

If I move the start date to one day in the future, then the slicer works as per normal.

Any ideas on why this is happening and how to fix it? This is on the desktop Version: 2.86.902.0 64-bit (October 2020)

[DonIncognito]

Hi Janey

My date table is generated with the following query.

let
    Source = List.Dates(StartDate, Length, #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"}}),
    StartDate = #date(2017, 7, 1),
    Today = DateTime.Date( DateTime.LocalNow()),
    Length = Duration.Days(Today - StartDate)+547,
    Custom1 = #"Renamed Columns",
    #"Fin Year Aus" = Table.AddColumn(Custom1, "Financial Year AUS",each Date.Year([Date]+#duration(184, 0, 0, 0))),
    #"Fin Month Aus" = Table.AddColumn(#"Fin Year Aus", "Financial Month AUS", each Number.Mod(Date.Month([Date])+6,12)),
    #"Inserted Year" = Table.AddColumn(#"Fin Month Aus", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "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])+1, Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Day of Week", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Week of Month", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    YYMM = Table.AddColumn(#"Inserted Week of Year", "YYMM", each ([Year]-2000)*100+[Month]),
    ID = Table.AddColumn (YYMM, "ID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
    #"Changed Type" = Table.TransformColumnTypes(ID,{{"Date", type date}, {"Financial Year AUS", Int64.Type}, {"Financial Month AUS", Int64.Type}, {"YYMM", Int64.Type}, {"ID", Int64.Type} }),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,12,Replacer.ReplaceValue,{"Financial Month AUS"}),
    #"Extracted Date" = Table.TransformColumns(#"Replaced Value",{})
in
    #"Extracted Date"

I am using the Date column from that for my slicer, and it the second date column in the pictures given.

That date column is linked 1:many to the Date_Of_Order column with cross filtering set to single

 

I have simplified it by taking out all information, other than the dates.

 

 

I get that if the date slicer has the entire range selected, then it wont filter the table. I just want to know if there is a way to configure the slicer or table so that it does.

Thanks again

Hugh

[v-janeyg-msft]

Hi, @DonIncognito 

The date range of your original date table is larger than the fact table. You can try to directly use the date of the fact table instead of the date table in the visual.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DonIncognito]

Hi @v-janeyg-msft

Thanks for your efforts, they are much appreciated, but it has not solved my issue. It got rid of the blank lines perfectly, but that was a side effect and not the main problem. I have cleaned up that issue, so now there are no lines with a blank date in them, but I'm still getting results returned from outside the dates set on the slicer.

[v-janeyg-msft]

Hi, @DonIncognito 

The problem may occur elsewhere.Whether the 'date' of the date slicer and the 'date' in the table are in the same field or in different tables?

Could you mind providing some sample data or more information about the table like relationships,fields and measure?So we can help you soon.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-janeyg-msft]

Hi， @DonIncognito 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure, then use it in filter pane.

Like this:

Measure = if(ISBLANK(MAX(Table1[date])),0,1)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DonIncognito]

So if I apply a filter to the date, it can still end up not being filtered?

So whats happening is the visual itself is filtered to only show dates after 1/1/2020, but when the full range is selected, its not filtering the date range at all? Is there a way to configure it so that the slicer returns the intended set of dates?

[amitchandak]

@DonIncognito , When there is no date selected and entire range is used. it means date is not filtered. so you will see all dates. The reason you more data is that your date table does not have all the required dates. and you are seeing a null row on top because of that. 

Also refer this - https://blog.crossjoin.co.uk/2019/07/10/power-bi-slicers-show-values-that-do-not-exist/