Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I think this is more a best practice design issue than anything else. Any advice would be gratefully received
I have a calculated calendar that gives me:
Date Fiscal Year Fiscal Year Start Date Fiscal Year End Date Open Hours
01/10/2017 2017 01/10/2017 30/09/2018 7.5
02/10/2017 2017 01/10/2017 30/09/2018 7.5
03/10/2017 2017 01/10/2017 30/09/2018 0
...
etc.
I also have a list of staff
Staff Member Start Date End Date
Joe 01/11/2017 -
Bob 01/04/2018 31/10/2019
Mary 01/01/2019 -
I basically want to calculate the percentage of a year that a person worked, i.e.
Staff 2017 2018 2019
Joe 89% 100% 100%
Bob 50% 100% 11%
Mary - - 25%
What would the best way to calculate this be?
Many thanks
Solved! Go to Solution.
Refer to this blog :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
From this, you can get a number of days the user has worked in and year by counting dates and then divide by number days should give you %
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @FrankMcQ ,
We can create a measure and used in a matrix visual to meet your requirement:
Percentage Of Work Hours =
VAR startDay =
MIN ( 'Staff List'[Start Date] )
VAR EndDay =
MAX ( 'Staff List'[End Date] )
RETURN
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Date] >= startDay
&& IF ( ISBLANK ( EndDay ), TRUE (), 'Calendar'[Date] <= EndDay )
)
)
/ SUM ( 'Calendar'[Open Hours] )
Or we can create three calculated columns in the staff list table:
2017 =
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = 2017
&& 'Calendar'[Date] >= [Start Date]
&& IF ( ISBLANK ( [End Date] ), TRUE (), 'Calendar'[Date] <= [End Date] )
)
)
/ CALCULATE ( SUM ( 'Calendar'[Open Hours] ), 'Calendar'[Fiscal Year] = 2017 )
2018 =
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = 2018
&& 'Calendar'[Date] >= [Start Date]
&& IF ( ISBLANK ( [End Date] ), TRUE (), 'Calendar'[Date] <= [End Date] )
)
)
/ CALCULATE ( SUM ( 'Calendar'[Open Hours] ), 'Calendar'[Fiscal Year] = 2018 )
2019 =
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = 2019
&& 'Calendar'[Date] >= [Start Date]
&& IF ( ISBLANK ( [End Date] ), TRUE (), 'Calendar'[Date] <= [End Date] )
)
)
/ CALCULATE ( SUM ( 'Calendar'[Open Hours] ), 'Calendar'[Fiscal Year] = 2019 )
If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best regards,
Apologies in the delayed response, I have spent the week understanding and implementing both solutions. I think I understand the issues much better now and you have both been a great help.
amitchandak you have implemented a really comprehensive solution that actually raised new functionality and feature requirements in my stakeholders - I have implemented your staff tracking chart in my internal solution.
Dong Li you provided a way that I could get to the figures I needed which is the annual estimate for staff costs.
I have amalgamated both solutions into a single pbix file which I have attached.
Unfortunately the solution I now have is based on a measure for each year - this is not the most effective when trying to chart trends.
Do you believe there is a better way to get to the final chart on the annual numbers page?
Many thanks for all your help!
Hi @FrankMcQ ,
We can create a measure and used in a matrix visual to meet your requirement:
Percentage Of Work Hours =
VAR startDay =
MIN ( 'Staff List'[Start Date] )
VAR EndDay =
MAX ( 'Staff List'[End Date] )
RETURN
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Date] >= startDay
&& IF ( ISBLANK ( EndDay ), TRUE (), 'Calendar'[Date] <= EndDay )
)
)
/ SUM ( 'Calendar'[Open Hours] )
Or we can create three calculated columns in the staff list table:
2017 =
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = 2017
&& 'Calendar'[Date] >= [Start Date]
&& IF ( ISBLANK ( [End Date] ), TRUE (), 'Calendar'[Date] <= [End Date] )
)
)
/ CALCULATE ( SUM ( 'Calendar'[Open Hours] ), 'Calendar'[Fiscal Year] = 2017 )
2018 =
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = 2018
&& 'Calendar'[Date] >= [Start Date]
&& IF ( ISBLANK ( [End Date] ), TRUE (), 'Calendar'[Date] <= [End Date] )
)
)
/ CALCULATE ( SUM ( 'Calendar'[Open Hours] ), 'Calendar'[Fiscal Year] = 2018 )
2019 =
CALCULATE (
SUM ( 'Calendar'[Open Hours] ),
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = 2019
&& 'Calendar'[Date] >= [Start Date]
&& IF ( ISBLANK ( [End Date] ), TRUE (), 'Calendar'[Date] <= [End Date] )
)
)
/ CALCULATE ( SUM ( 'Calendar'[Open Hours] ), 'Calendar'[Fiscal Year] = 2019 )
If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best regards,
Refer to this blog :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
From this, you can get a number of days the user has worked in and year by counting dates and then divide by number days should give you %
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |