Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi,
Share some data and show the expected result.
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.
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] )
Thanks for the reply but it still throwing error for me..
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
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
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.
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?
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 ?
You are welcome. What do you mean by "enable us to itearate in resource and calendar table"?
Please refer to my HR blog for this
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |