Power BI – How and Why to Add Records Between the Start and End Date

If you have data that includes date ranges (such as tasks or projects with a start and end date) and create a visual in Power BI with either a start or end date, it will often give you incorrect results.

Let’s have a look at an example below:

The first row shows 2 hours forecasted per day between January 3^rd and January 7^th. The data itself is correct, but is that what I want to see in my report as a project manager? No, I want to see 2 hours for each day between January 3^rd and January 7^th, 10 hours (2 x 5 days) instead.

How can we do this in Power BI without altering the source of data?

What you need to do is transform your data. There are many methods you can use, but I’ll show you one simple method to solve the problem.

Generating new records/rows between dates including start and end date:

• Click Transform data to open power query editor.

• Make sure the type of your dates is the “date” type.
• Click Add Column tab > Custom Column.

• Enter the name of column and expression below. {Number.From([Start Date])..Number.From([End Date])}
• Please note that the date is a number as well.
• Two dots will generate a list from Start Date and End Date.

• Click arrow button on newly created column Date and select “Expand to New Rows”.

• You will find the same records/rows but different numbers of date from the list.

• Change type to Date.

• Create a relationship to the date with your calendar dimension table in your model. The result below is what I want to see in my report:

If you have any questions about using Power BI, please connect with us.