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

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

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's avatar
anandav
Skilled Sharer
8 years ago

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

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    You are welcome.