Reply
Established Member
Posts: 169
Registered: ‎01-11-2017
Accepted Solution

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.

Capture.JPG

 

The transformation I need is as below:

Traget.JPG

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.


Accepted Solutions
Highlighted
Super User
Posts: 2,598
Registered: ‎11-29-2015

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

cal.jpg

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Super User
Posts: 2,955
Registered: ‎01-14-2017

Re: Adding rows between start and end dates

Hi,

 

You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg

 

Hope this helps.

View solution in original post


All Replies
Highlighted
Super User
Posts: 2,598
Registered: ‎11-29-2015

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

cal.jpg

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Established Member
Posts: 169
Registered: ‎01-11-2017

Re: Adding rows between start and end dates

@Phil_Seamark,

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!

Established Member
Posts: 169
Registered: ‎01-11-2017

Re: Adding rows between start and end dates

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:

DataModel.jpg

 

What I need (as inidicated in red):

ResCalc.jpg

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.

Super User
Posts: 2,955
Registered: ‎01-14-2017

Re: Adding rows between start and end dates

Hi @anandav

 

Share your file download link here.

Established Member
Posts: 169
Registered: ‎01-11-2017

Re: Adding rows between start and end dates

Hi @Ashish_Mathur,

 

The source file is simple as described in the post.

IDTask NameResourceAllocation %Start DateEnd Date
1Task 1Res14001/09/201720/09/2017
2Task 2Res24001/09/201720/09/2017
3Task 3Res14001/10/201720/10/2017
4Task 4Res34001/10/201720/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.

Super User
Posts: 2,955
Registered: ‎01-14-2017

Re: Adding rows between start and end dates

Hi,

 

You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg

 

Hope this helps.

Established Member
Posts: 169
Registered: ‎01-11-2017

Re: Adding rows between start and end dates

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!

Super User
Posts: 2,955
Registered: ‎01-14-2017

Re: Adding rows between start and end dates

You are welcome.

Established Member
Posts: 169
Registered: ‎01-11-2017

Re: Adding rows between start and end dates

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.