In order to slice dates the way I need to, I use a code to add a rowfor each day between a start and stop date in my table. The table looks something like this:
Person Service Start date Stop date
1 a 15-06-2016 27-10-2016
2 b 21-04-2016 06-10-2016
3 c 23-08-2016 17-01-2017
and the code I add to this to create a row for each day between start and stop is
#"Added DaysList" = Table.AddColumn(#"Added something", "DaysList", each
List.Dates([Start date], 1 + Duration.TotalDays([Stop date] - [Start date]), #duration(1,0,0,0))),
#"ExpandedDaysList" = Table.ExpandListColumn(#"Added DaysList", "DaysList"),
#"Changed type DaysList" = Table.TransformColumnTypes(#"ExpandedDaysList",{{"DaysList", type date}})My problem is when a service is still running (aka no stop date). My data creates the date "31.12.9999" to indicate a missing stop date, and that's obviously going to give me a LOT of unnecessary rows, especially since I use COUNT measures to find out how many days people have received a specific service (number of DaysList).
So (I think) what I need to do is figure out how to code "if [Stop date] = "31.12.9999" then [Stop date] = "isn't finished yet".
But of course, that's going to give me problems with my slicer, since "isn't finished yet" is text and the rest are dates...
I tried just doing a simple calculated column
#"Added New Stop Date" = Table.AddColumn(#"Added Something else here", "New Stop date", each if
[Stop date] > DateTime.Date(DateTime.LocalNow()) then
DateTime.Date(DateTime.LocalNow()) else
[Stop date], type date),which makes my slicer sing (since I'm not interested in all the days after today when I'm counting how long someone has been getting a service), but that's not telling me who really stopped today and who just has today as stopdate because they're not finished yet.
I hope I'm making a bit of sense, and someone can help me figure out how to limit the DaysList?
