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