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,
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.
Hi anandav,
This should be the calculated column formula in the Data Model i.e. PowerPivot
=CALCULATE(SUM(Data[Allocated hours]),FILTER(Data,Data[Resource]=EARLIER(Data[Resource])&&Data[Date]=EARLIER(Data[Date])))
Thereafter, write this simple measure
=SUM(Data[Column])
Hope this helps.
Hi Ashish_Mathur,
One more help.
How can I achieve the same (total hours per resource per day) in a column?
The measure works well for Matirx. But I needed to create a new Column which does the same thing as Hours Worked 1 measure.
The requirement is so that the reosurce manager can filter by resource availability to see which dates are a given resource is available.
Hope you can help.
Hi,
i am not sure of what you want but just swith your visual from a matrix to a table and the hours worked1 will become a column which can now be filtered.
Hi Ashish_Mathur,
What I wanted to do is to create a column instead of measure.
So instead of Hours Worked 1 as a measure, I would like to create a new colum.
Hi anandav,
What purpose will that serve that the Table visual wouldn't? Wouldn't your requirement be met by the Table visual which i suggested above?
Hi Ashish_Mathur,
Yes, the measure serves the purpose.
I am keen to learn the DAX code for how this could be done using a column.
Thanks Ashish_Mathur
You are welcome.