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

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
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
    9 years ago

    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'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.

  • 1320Data's avatar
    1320Data
    Frequent Visitor
    3 years ago

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

      Hi,

      It is probably the Date field from the Calendar Table.