Need Dax help for resource availability

[PradeepDive]

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. 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-janeyg-msft]

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]

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

[PradeepDive]

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

[v-janeyg-msft]

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]

I can not post my pbix due to org security, below is my data model

Calendar :

Date	Week Start Date	Daily Available hrs
15-Oct-2020	12-Oct-2020	8
16-Oct-2020	12-Oct-2020	8
17-Oct-2020	12-Oct-2020	0

Resource Table

Resource id	Hire Date	Termination Date
ID 1	1-Jan -2020	4-Apr-2020
ID 2	19-May-2020

Relationship between Calendar & Resource table is  DATE >> Hire Date   (INACTIVE Relationship)

What I am trying to achive is a Dax which will calculation sum of daily hours from calendar table for each resource based on hire and termination date.  For example if termination date is not blank the till the available end of calendar date and if termination date is present then between hire and termination date.

My data model uses Calendar dates for all  date calculations. Is there a way to create a DAX which iterates in Resource as well as calendar to get those details for me.. also on all granual level I slice it.

Hope that explained my desired result.  Thanks 

[v-janeyg-msft]

Hi, @PradeepDive 

I deleted the ‘allselected’ in my previous formula and create this visual to show.

Here is my sample file.Hope to help you.

If it doesn’t solve your problem, 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.

[Ashish_Mathur]

Hi,

We can transform the last 2 date columns of the Resource table into a single Date column with one row for each date (per Resource ID).  Thereafter we can create an active relationship between the Date column in the Resource table to the Date column in your Calendar table.  Are you amenable to this approach?

[PradeepDive]

Hi Ashish,

Thanks for the reply.

I can not create an active releationship between both table due to earlier multiple other relationships with othere tables.

Also, curios to know how that approach would enable us to itearate in resource and calendar table ?

[Ashish_Mathur]

You are welcome.  What do you mean by "enable us to itearate in resource and calendar table"?

[amitchandak]

@PradeepDive ,

Please refer to my HR blog for this

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://www.youtube.com/watch?v=e6Y-l_JtCq4

There I have one active and one inactive join. You can do minor changes as per need

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[PradeepDive]

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.