Forum Discussion

anandav's avatar
anandav
Skilled Sharer
9 years ago
Solved

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.   The transformation I need is as below: As you can see I need ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

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

     

anandav's avatar
anandav
Skilled Sharer
8 years ago

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's avatar
Ashish_Mathur
Super User
8 years ago

Hi anandav

 

Share your file download link here.

  • anandav's avatar
    anandav
    Skilled Sharer
    8 years ago

    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.

      • anandav's avatar
        anandav
        Skilled Sharer
        8 years ago

        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!