How to list all dates between two dates in PowerBI and distribute numbers evenly among them.

Consider a scenario where a start and end date along with the total duration for a particular task has been given to us. We have to distribute the total duration equally among all dates between the start and end date. We can solve this issue by the combination of Power Query and DAX. Lets see the steps

1. First we need to generate the list of dates from start date till end date.

• Open Advance Editor

• As you can see the table I’m working on have two columns for start and end date for a particular task respectively.

• In Power Query we cannot generate a list between dates so first we have to convert the data type from date to numeric. This can be easily done by right clicking the desired column and changing the data type right away.

• After the data type of respective columns is changed into numeric, click on the “Add Column” option in the ribbon and select custom column.
• To generate a list in Power Query the syntax is “starting number .. Ending number” so we apply this syntax in accordance to our needs. The “Number” function is make to sure to take only numeric values to avoid any conflicts.

• After validating the code press the “OK” button. You can see a new column with lists. Click on the Expand button on the top right of the column.

• After Expanding the column you’ll see a list of numbers.

• Since these numbers are numeric we have to again convert them in Date format. This can be done by right clicking on the column and changing its Data Type.

• As you can see we can see all the dates between start and end dates now.

2. Since we generated the list of dates. We Proceed to distribute duration equally

• Create a new Calculated Column in PowerBI Desktop
• Write the following DAX.

actual hours = Sheet1[original estimate]/ CALCULATE(count(Sheet1[taskid]),FILTER(Sheet1,Sheet1[taskid]=EARLIER(Sheet1[taskid])))

• This code divides the Duration assigned for a task by the count of the total tasks where the task id is same.

• As you can see the Original Estimate column which is for total duration for a task is equally divided into a new column called “actual hours”. You cant see dates of Saturday, Sunday as I filtered these dates in Advanced Editor itself as they are non working days. This can be modified according to requirement.
• Thank you for reading hope this article helped