Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
Hey @v-lili6-msft thanks for looking at this. I came to the same conclusion, should have thought of it earlier 😉
Thanks 🙂
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |