Creating DaysLIst rows between start/stop dates, while limiting how many? [M]

[grggmrtn]

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?

[v-lili6-msft · Accepted solution]

hi, @grggmrtn 

You could add another flag column for that's who really stopped today and who just has today as stopdate after you doing a simple calculated column.

Then use this column for slicer or as a flag.

If not your case, please share more sample data and your expected output.

Best Regards,

Lin

[grggmrtn]

Hey @v-lili6-msft thanks for looking at this. I came to the same conclusion, should have thought of it earlier 😉

Thanks 🙂