Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anandav
Skilled Sharer
Skilled Sharer

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.

2 ACCEPTED SOLUTIONS
Phil_Seamark
Employee
Employee

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

Hi,

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19
nchambe
Advocate II
Advocate II

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

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!

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

 

Hi,

It is probably the Date field from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

Hi @anandav

 

Share your file download link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.