Forum Discussion
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.
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)) )Hi,
You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg
Hope this helps.
19 Replies
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)) )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!
Hi Phil_Seamark,
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.
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 advanceHi,
It is probably the Date field from the Calendar Table.
Please refer to my post here, which will guide you through step-by-step "expanding date ranges" so there's a row for each date.
