Adding rows between start and end dates

[anandav]

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.

[Phil_Seamark · Accepted solution]

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

[nchambe]

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.

https://sharepointlibrarian.com/2018/08/08/how-to-add-rows-for-dates-between-start-and-end-dates-in-...

[Phil_Seamark · Accepted solution]

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

[1320Data]

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 advance

[Ashish_Mathur]

Hi,

It is probably the Date field from the Calendar Table.

[anandav]

@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!

[anandav]

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.

[Ashish_Mathur]

Hi @anandav

Share your file download link here.

[anandav]

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.

[Ashish_Mathur · Accepted solution]

Hi,

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

Hope this helps.

[anandav]

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!

[Ashish_Mathur]

You are welcome.

[anandav]

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.

[Ashish_Mathur]

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.

[anandav]

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.

[Ashish_Mathur]

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?

[anandav]

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.

[Ashish_Mathur]

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.

[anandav]

Thanks @Ashish_Mathur

[Ashish_Mathur]

You are welcome.