Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
grggmrtn
Post Patron
Post Patron

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

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?

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

 

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

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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

 

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

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

 

Thanks 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.