Forum Discussion
Adding rows between start and end dates
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.
Hi Ashish_Mathur,
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.
Hi,
You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg
Hope this helps.
Hi Ashish_Mathur,
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!
You are welcome.