Need a slicer to chose a single date (which is between two date columns)

[grggmrtn]

In an effort to make our reports more readable, I need to simplify the way we work with dates.

Our data looks more or less like this:

Personnumber	ServiceName	Date.Start	Date.Stop
1		Support		1-01-2016	02-10-2018
2		Training	05-12-2017	12-12-2018
3		Housing		3-03-2005	07-08-2010
4		Vehicle		6-12-2015	31-12-9999
5		Meeting		12-05-2014	12-10-2015

Right now we use a combination of two slicers to choose the date we're interested. For example, if I want to know how many services were active on 05-05-2018, I would use Slicer1 to show all start dates before 05-05-2018 and Slicer 2 to show all stop dates after 05-05-2018.

It's just not very user friendly.

Is there any way to make a slicer that would let me choose ONE date, which would check to see if the chosen date is between Date.Start and Date.Stop?

In time we hope to expand upon the idea and create more dynamic tables, where we could use some sort of slider to update the tables in real time depending on the date chosen...

Thanks in advance!

[LivioLanzo · Accepted solution]

@grggmrtn

you can apply this transformation to the data and then link the date table to the column 'DaysList'

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY4xDoMwDEXv4hnJcYKB3qBLpyKWKENVRW2kChCE+9fGHerp+f8nJzECQQP3Y12XrQoREnpHnaJDrzxAaiJ4ScbtUeYyv7T0yFr2xvRnBomuy7GbGDBI5Viwx0Etd1qtBFN+l+cn24lOOzYOhBeZU9TolnO1c/x7qrUP2sKQ0hc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Personnumber = _t, ServiceName = _t, Date.Start = _t, Date.Stop = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Personnumber", Int64.Type}, {"ServiceName", type text}, {"Date.Start", type date}, {"Date.Stop", type date}}),
    AddedCustom = Table.AddColumn(ChangedType, "DaysList", each List.Dates([Date.Start], 1 + Duration.TotalDays([Date.Stop] - [Date.Start]), #duration(1,0,0,0))),
    ExpandedDaysList = Table.ExpandListColumn(AddedCustom, "DaysList"),
    RemovedColumns = Table.RemoveColumns(ExpandedDaysList,{"Date.Start", "Date.Stop"}),
    ChangedType1 = Table.TransformColumnTypes(RemovedColumns,{{"DaysList", type date}})
in
    ChangedType1

[grggmrtn]

Holy crap it worked!

i may or may not have spent the good part of today "translating" your transformation to our database (the syntax was murder!) but I got it done 🙂 - Thanks so much!

For future reference to myself: I also added a line where I change all dates > today to today's date. Our database uses 31-12-9999 as "null" so it took way too much time, and anything > today isn't relevant.

    #"AddedCustom1" = Table.AddColumn(#"Ændret type", "Indsatsdetaljer.Indsats periode stop NY", each if [Indsatsdetaljer.Indsats periode stop] > DateTime.Date(DateTime.LocalNow()) then DateTime.Date(DateTime.LocalNow()) else [Indsatsdetaljer.Indsats periode stop]),

[OSS]

Just select one of this columns for a slicer.  to boxes will appear. an you would be able to choose start and finis date in that box