Forum Discussion

PradeepDive's avatar
PradeepDive
Icon for Helper II rankHelper II
5 years ago

Need Dax help for resource availability

Hi All,

 

I am not able to write a Dax formula to calculate resource capacity based on hire / termination date.  My Model is simple with two tables. 

1st is Resource Table - where all the resource details are present like name, dept, hire date, termination date etc.

2nd is Calendar Table - This table is with usual dates as normal calendar has along with daily hours availability (8 hrs)

 

Resource and Calendar table has an inactive relationship  between hire date and date.

 

I am trying to write a simple DAX to calculate resource availabilty which basically aggregate availble hours from Calaendar table based on resource hire date.  Also would let me slice and dice it as per requirement ( like yearly, monthly,weekly etc)

 

I know this is something very easy but I am not able to get the correct result. 

 

Any idas ?  Thanks in advance. 

Need Help

12 Replies

    • PradeepDive's avatar
      PradeepDive
      Icon for Helper II rankHelper II
      5 years ago

      Hi Amit,

       

      Thank you for the quick reply. I already checked your earlier post for HR Analytics but I am not able to get the sum of hours from calendar table.  I tried to twick your DAX but no luck.

       

      How do I get a sum of hours from calendar table by referring hire date from resource table ? 

       

      Any help is appreciated. 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, PradeepDive 

     

    It’s my pleasure to answer for you.

    Based on your data, I created a calendar table and calculated it according to hours=8,create a measure like this:

    totalhours = 
VAR tab2 =
    ADDCOLUMNS (
        ALLSELECTED(  ResourceTable ),
        "tthours",
            VAR tab =
                ADDCOLUMNS (
                    FILTER (
                        CALENDARAUTO (),
                        [Date] IN DISTINCT ( 'Dates'[Date] )
                            && [Date]
                                IN CALENDAR ( ( ResourceTable[hire date] ), ResourceTable[termination date] )
                    ),
                    "totoalhours", LOOKUPVALUE ( 'Dates'[hours], 'Dates'[Date], [Date] )
                )
            RETURN
                SUMX ( tab, [totoalhours] )
    )
RETURN
    SUMX ( tab2, [tthours] )

    If it doesn’t solve your problems, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • PradeepDive's avatar
      PradeepDive
      Icon for Helper II rankHelper II
      5 years ago

      Thanks for the reply but it still throwing error for me.. 

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, PradeepDive 

        Not very clear what final results you want to present,Would you mind providing your .pbix file? If not, please provide a sample data and desired result .

         

        Best Regards

        Janey Guo

    • PradeepDive's avatar
      PradeepDive
      Icon for Helper II rankHelper II
      5 years ago

      It worked a bit but not able to get hire and termination date from resource table as I have inactive relationship between these two tables.

       

      so the formula is breaking here

      IN CALENDAR ( ( ResourceTable[hire date] ), ResourceTable[termination date] )