Forum Discussion

anandav's avatar
anandav
Skilled Sharer
9 years ago
Solved

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 ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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))
             )

     

1320Data's avatar
1320Data
Frequent Visitor
3 years ago

Hi,

 I am trying to solve some similar kind of problem so I am trying to understand your code.

Table1'[Start Date] <= [Date] && 'Table1'[End Date] >= [Date] 
Can you explain which Date column are we refering here ?

TThan you in advance

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

It is probably the Date field from the Calendar Table.