## Desktop

Established Member
Posts: 169
Registered: ‎01-11-2017

# Adding rows between start and end dates

Hi All,

I am trying to generate a resource allocation map/detail for a given task based on the below sample details.

The transformation I need is as below:

As you can see I need to insert one row for the combination of ID, Taks name, Resource and allocation hours per each date that falls between start and end date inclusive.

Any help and/or suggestions on the above problem will be much appreciated.

Thanks.

Accepted Solutions
Highlighted
Super User
Posts: 2,598
Registered: ‎11-29-2015

## Re: Adding rows between start and end dates

Hi @anandav

This calculated table might get close :

```Table =
CALENDARAUTO() ,
"Day Type" , IF(WEEKDAY([Date])<6,"Weekday","Weekend")
)

RETURN
FILTER(
CROSSJOIN(
myCalendar ,Table1) ,
'Table1'[Start Date] <= [Date] && 'Table1'[End Date] >= [Date]
),
"Allocated Hrs",IF([Day Type]="Weekend",0,8 * ('Table1'[Allocation %]/100))
)```

Proud to be a Datanaut!

Super User
Posts: 2,955
Registered: ‎01-14-2017

## Re: Adding rows between start and end dates

Hi,

You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg

Hope this helps.

All Replies
Highlighted
Super User
Posts: 2,598
Registered: ‎11-29-2015

## Re: Adding rows between start and end dates

Hi @anandav

This calculated table might get close :

```Table =
CALENDARAUTO() ,
"Day Type" , IF(WEEKDAY([Date])<6,"Weekday","Weekend")
)

RETURN
FILTER(
CROSSJOIN(
myCalendar ,Table1) ,
'Table1'[Start Date] <= [Date] && 'Table1'[End Date] >= [Date]
),
"Allocated Hrs",IF([Day Type]="Weekend",0,8 * ('Table1'[Allocation %]/100))
)```

Proud to be a Datanaut!

Established Member
Posts: 169
Registered: ‎01-11-2017

## Re: Adding rows between start and end dates

Thank you for the prompt reply. It works fine.

You are truly a "super contributor" taking time to help with such detail code.

I tried the DAX code but I get an error "CalendarAuto function can not find a base column type of DateTime type in the model" and then figured out I haven't set the data type to Date in my original table - from one of your another post.

Thanks a lot!

Established Member
Posts: 169
Registered: ‎01-11-2017

## Re: Adding rows between start and end dates

I have a matrix which displays the resource allocations from the table created from the code you gave.

Since matrix does not allow conditional formatting on row subtotals, I was trying to add a measure to calculate the daily total allocated hours for a given resource for a given day. But I could not get the measure work correct.

Data Model:

What I need (as inidicated in red):

Then I can disable row subtotals and do conditional formatting on the Total Hours column.

I cannot figure out how to write the measure with the right filters using CALCULATE.

Any help will be greatly appreciated.

Super User
Posts: 2,955
Registered: ‎01-14-2017

## Re: Adding rows between start and end dates

Hi @anandav

Established Member
Posts: 169
Registered: ‎01-11-2017

## Re: Adding rows between start and end dates

The source file is simple as described in the post.

 ID Task Name Resource Allocation % Start Date End Date 1 Task 1 Res1 40 01/09/2017 20/09/2017 2 Task 2 Res2 40 01/09/2017 20/09/2017 3 Task 3 Res1 40 01/10/2017 20/10/2017 4 Task 4 Res3 40 01/10/2017 20/10/2017

Based on Phil's DAX code this source is tranformed in Resource Allocation table (see above post by Phil).

I am generating the matirx based on this dynamically created table.

Not sure I have addressed what you needed but hope it is clear.

Super User
Posts: 2,955
Registered: ‎01-14-2017

## Re: Adding rows between start and end dates

Hi,

You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg

Hope this helps.

Established Member
Posts: 169
Registered: ‎01-11-2017

## Re: Adding rows between start and end dates

Hours Worked 1 is what I wanted. Thanks for the quick solution and taking time to put out a solution file.

I was strugling on the filter part of the CALCULATE function and ALL(taskName) removes the filter on Task Name but keeps the Date filter. Excatly what I needed.

Thanks a lot!

Super User
Posts: 2,955
Registered: ‎01-14-2017

## Re: Adding rows between start and end dates

You are welcome.

Established Member
Posts: 169
Registered: ‎01-11-2017

## Re: Adding rows between start and end dates

One more help.

How can I achieve the same (total hours per resource per day) in a column?

The measure works well for Matirx. But I needed to create a new Column which does the same thing as Hours Worked 1 measure.

The requirement is so that the reosurce manager can filter by resource availability to see which dates are a given resource is available.

Hope you can help.