In Power BI there are several custom visuals available, such as Elastacloud’s Calendar Visual, that show the density of events over time. However, if your data includes date ranges (start date with a different end date, such as task/project lists), visuals like these will only understand one of the two dates (whichever you’ve chosen for the data value) and none of the dates in between will be accounted for, making your data visualization incorrect.
You can, however, “expand” these date ranges or durations to create rows for all the dates including start, end and those in between. This way each date that’s part of the range is then graphed appropriately.
- Click “Edit queries” to open Power Query editor
- Make sure your date columns are date (not date/time) format
- Create a custom column
- Change the name to “Date” and paste the following after the equals (“=”) sign:
{ Number.From([EventDate])..Number.From([EndDate]) }
- Click OK
- Click the “diverging-arrow” icon next to “Date” and click “Expand to new rows”. Notice the addition of new rows.
- Change the column type to “Date”
- You may wish to delete your “start date” and “end date” columns but certainly don’t need to.
- Click “Close and Apply”
- Use your new “Date” column instead of “start date” or “end date” in your visualization pane values. Notice how more squares are filled in for dates that fell into ranges previously unaccounted for.
Hi, I have question, this solution was awesome, but now I need to count the employee IDs who has a certain date as the start date, given that the ids repeat and the start date also repeat with the creation of the new rows it has become difficult.
The expanded column i am calling “transaction date”, so what I need to do is something like “countdistinct(employee id),filter(all(date),firstdate(transaction date)” and put it in a card that will be filtered by the rest of the viz (month or week ending). I want to know how many people started working on a certain period.
As of now it looks to be counting every time a date within the month or week ending appears vs. counting only if it is the first date that it appears for a certain employee id.
Is it possible to add just months, not days, between the dates
Hi, did you find the solution in order to have months and not days? thanks in advance
I am so impressed at how simply you laid out this solution. Have been looking for something like this for eons. Thank you.
is there a way to make it ignore entries that star date and end date are “null” its giving me an error because of that
Thank 🙂
Worked a treat!
I’m Following the instructions, and do get dates, however, I do not get any new rows. What am I doing wrong?
Is it possible to break down also including hour?
So helpful, thank you
Hi, this is great! But i need it to work with minutes to, is this possible?
Did your find any solution? I am having same issue like you are searching for also
This is unbelievable! I’ve been pulling my hair trying to find this answer. Thank you a million times!
Hi, thanks for article. It was very helpful. However a whole number field I had was not divided into equal parts. Is there a way I can do this?
Ejo: I walked into this now, almost a year later. I’m quite noob with this but my solution is that before you expand, you substract the end date-start date, then add custom column 1 and divide this with the previous number.
You will get a number 0,00x…. on each row, then you can use this as a multiplier to multiply your budget, etc. numbers.