Advertisements

How to add rows for dates between start and end dates in Power BI date range data

screenshot1

Screenshot from appsource.microsoft.com’s app detail page.

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.

  1. Click “Edit queries” to open Power Query editor
    PBIDesktop_2018-08-08_14-49-57
  2. Make sure your date columns are date (not date/time) format
    2018-08-08_14-53-42
  3. Create a custom column
    PBIDesktop_2018-08-08_14-56-19
  4. Change the name to “Date” and paste the following after the equals (“=”) sign:
    { Number.From([EventDate])..Number.From([EndDate]) }

    2018-08-08_15-14-54

  5. Click OK
  6. Click the “diverging-arrow” icon next to “Date” and click “Expand to new rows”. Notice the addition of new rows.
    2018-08-08_15-23-49
  7. Change the column type to “Date”
    2018-08-08_15-25-21
  8. You may wish to delete your “start date” and “end date” columns but certainly don’t need to.
  9. Click “Close and Apply”
    PBIDesktop_2018-08-08_15-26-27
  10. 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.
    2018-08-08_15-30-31
Advertisements

1 Comments

  1. ejo

    Reply

    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?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: