Adding rows between start and end dates

[anandav]

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.

[Phil_Seamark · Accepted solution]

Hi @anandav

This calculated table might get close :

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

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