Convert date ranges into list of dates

By: Bemint

3   0   194

Uploaded on 02/17/2017

Answer to a question on the Power BI Community forum.

Comments (4):

By anonymous    2017-09-20

To create such a visual, I'd recommend an area chart (or stacked area chart) with the date on the axis, the unit name on the legend, and the capacity on the values. You could also do it as a stacked column chart too. However, then each date will be broken into discrete columns. See below image.

In terms of data manipulation, you'll need to convert the data with the date ranges you have above into a row for each individual date & unit. E.g. the first row, instead of being 11/2 to 13/2, would be expanded into 3 rows, one for each date.

You can do this in Power Query as you bring the data into Power BI Desktop, or in DAX after bringing it in. There are several solutions to this outlined in this thread (https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418), but personally, I recommend the technique (and video) posted by MarcelBeug (https://youtu.be/QSXzhb-EwHM).

You'll also want an independent list of dates (with no gaps) to join the final date column to - otherwise your visual will skip dates when no units had capacity. By default, the chart will begin on the first date with data and end on the last date with data, so in that sense it is dynamic, but you can add a date slicer to give the end-user more control.

enter image description here

Area chart on top, column chart on bottom, date slicer on right filtering Jan-Mar.

Original Thread

By anonymous    2017-09-20

What you need to do is relatively complicated, but the summary of my solution is:

  1. Calculate the per-day consumption
  2. Calculate the start and end date of each reading (e.g. the previous reading date plus one day, and the reading date)
  3. Expand your data to have 1 row per day rather than 1-row per reading

You want to do these steps before you load the data into your data model (i.e. in your source system, or as the data is loaded using the Query Editor/Power Query).

Below, I assume you're using the Query Editor/Power Query. However, if you can use your source system, it's often the better choice (since the source system may be a database that is vastly faster than your desktop).

Note that your No. of Days calculation doesn't make sense to me. There are more than 38 days between 24 Jan 2016 and 31 Mar 2016. There are also more than 13 days between 10 Jan and 24 Jan. For this reason, it was difficult to tell whether you wanted a new reading to count on the day the previous reading was taken, or on the next full day. I assume the former. Also note, I've proceeded on the basis that your No. of Days calculation is correct

Calculate the Per Day Consumption

This is the easiest step, given that you have already calculated the Consumption and the No. of Days. Just divide one by the other. In the Query Editor, you can click in the Consumption (gallons) column and select Add Column > Standard > Divide. Under Value, choose Use values in a column and then select the No. of Days column.

Divide by No. Of Days

Calculate the Start & End Date of Each Reading

The date of the reading is the end date, so you can rename Date to be End Date (since a reading is applied retroactively).

For the start date, in the Query Editor, you will need to add an index column (Add Column > Index Column). You will want to make sure your data is sorted by Meter ID and Date Ascending before doing this. Call the column Index.

Next, Add Column > Custom Column and pull the reading date from the prior row. Call the new column Previous End Date for now.

// A try is necessary because we can't get the previous row if there is no previous row (we'll get an error, which we can handle in the 'otherwise' block)
try 
  if 
    // See if the previous row is for the same Meter ID
    [Meter ID] = #"Reordered Columns"{[Index] - 1}[Meter ID] 
  then 
    // If it is, grab the Reading Date from the previous row
    #"Reordered Columns"{[Index]-1}[End Date] 
  else 
    // If this is the first reading for a meter, calculate the Start Date by subtracting the No. of Days from the End Date
    Date.AddDays([End Date], -[No. of Days])
otherwise
  // If this is the first row in the table, also calculate the Start Date by subtracting the No. of Days from the End Date
  Date.AddDays([End Date], -[No. of Days])

Next, you'll want to add 1 to the Start Date, as we want the reading to apply to the day after the previous reading, not on the day of the previous reading.

Date.AddDays([Previous End Date], 1)

Note, if you want the reading date to count in the prior period, subtract 1 from the End Date rather than add 1 to the start date (previous end date).

Expand your data to have 1 row per day

At this point, you should have a Meter ID, Start Date, End Date, and per day consumption column that reflects what you expect (i.e. the per day consumption is correct for the date range).

Data so far (assuming your No. of Days and Per Day Consumption columns are correct, which they aren't

The final step is to duplicate each row for each date in the date range. There are several solutions to this outlined in this thread (https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418), but personally, I recommend the technique (and video) posted by MarcelBeug (https://youtu.be/QSXzhb-EwHM).

You should end up with something more like this (after some removing & renaming of columns):

Final data model

Finally

Now that you have one row per meter & date, with a per day consumption already calculated, you can build a visual. For example, you could do a column chart with Date on the Axis, and Consumption per Day as the value. By default, Power BI will recognize that Date is a date, and will roll it up by Year-Quarter-Month-Day. Press the little 'x' by Year and Quarter, and you'll have a chart that sums up the per day consumption by month. You can also drill down to individual date.

Final chart

Further Reading

  1. Reading a value from a previous row in Power Query
  2. If Statements in Power Query
  3. The AddDays function in Power Query
  4. Adding Comments in Power Query
  5. Catching Errors in Power Query
  6. Converting a date range into a list of dates (Marcel Beug's solution)
  7. A similar problem I previously answered

Original Thread

Recommended Books

    Submit Your Video

    If you have some great dev videos to share, please fill out this form.