Show all values except values that are filtered out by time slicer

[Anonymous]

Hi all,

I have a table:

Created date / Name / Closed date

1.1.2019 / project A / <none>

1.1.2019 / project B / 20.2.2019

2.1.2019 / project C / <none>

10.2.2019 / project D / 3.3.2019

I have a time slicer. When user select a date interval, user expect to see only ongoing projects.

E.g.

If user selects 1.1.2019 - 1.2.2019, the result should be:

1.1.2019 / project A / <none>

1.1.2019 / project B / 20.2.2019

2.1.2019 / project C / <none>

if user selects 1.2.2019 - 1.3.2019, the result should be:

1.1.2019 / project A / <none>

2.1.2019 / project C / <none>

10.2.2019 / project D / 3.3.2019

Any idea how i can do this? Thank you for help!

[Nolock · Accepted solution]

Hi @Anonymous,

all solutions I've seen were about creating a list of all dates between Created date and Closed date and using the slicer on the new field. But it works only if your range has both timestamp, a start and an end one.

let
    Source = FactTableOrig,
    #"Added ListOfDatesBetweenFromDateAndToDate" = Table.AddColumn(Source, "ListOfDatesBetweenFromDateAndToDate", each {Number.From([FromDate])..Number.From([ToDate])}),
    #"Expanded ListOfDatesBetweenFromDateAndToDate" = Table.ExpandListColumn(#"Added ListOfDatesBetweenFromDateAndToDate", "ListOfDatesBetweenFromDateAndToDate"),
    #"Changed Type To Date" = Table.TransformColumnTypes(#"Expanded ListOfDatesBetweenFromDateAndToDate",{{"ListOfDatesBetweenFromDateAndToDate", type date}}),
    #"Rename New Column To InRangeDate" = Table.RenameColumns(#"Changed Type To Date",{{"ListOfDatesBetweenFromDateAndToDate", "InRangeDate"}})
in
    #"Rename New Column To InRangeDate"

And then in Power BI Desktop apply the slicer on the new InRangeDate field: