Count total number of resources by Financial Year

Hi Team,

This is my very first topic in this forum, so please bare with me on this. I have built a dashboard for my project’s HR team but they want one function which I can’t seem to do. I have a project resources table that contains Resource Names, Start Date and Finish Date. I need to create a table or a graph that shows the total number of resources by financial year based on the start and finish dates excluding the vacant positions. Here is an example:

In the above image:

  1. Blue table (“Resource Plan”) - contains the resources details (sample data only)
  2. Orange table - this is the data I want to create through a measure or something
  3. Graph - Final result

Basically, a resource can be employed over number of years and I need to represent that using start and finish dates. Also note oor financial year goes from July to June (ie 1/7/19 to 30/6/20)

I tried to come up with a solution by creating the following measure but it only gives the total where the financial year relates to the start date. It doesn’t provide a count for every financial year though to end date.

Employee Count =
VAR MaxDate = MAX ( ‘Calendar’[Date] )

VAR EmpCnt =
CALCULATETABLE ( ‘Resource Plan’, ‘Resource Plan’[Start Date] <= MaxDate, ALL ( ‘Calendar’[Date] ) )),
(ISBLANK ( ‘Resource Plan’[End Date]) || ‘Resource Plan’[End Date] > MaxDate))
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )

Thank you in advance for your help. Much appreciated :slight_smile:

Hi @tt.xavier welcome to the forum!

I’ve created a Date table with a FY year starting on July 1st and Marked that as a date table.

Created 2 inactive relationships between the Date table and the tData table.

See for more details around this and the limitations, please see:

Next Created the following measure:

Employee Count = 
        NOT( tData[Name] ) in {"Vacant"} && 
        tData[StartDate] <= MAX('Date'[Date]) &&
        tData[EndDate] >= MIN('Date'[Date])

With this result:

Now I noticed that this doesn’t match your results exactly but that’s because you didn’t seem to account for the FY starting on July 1st…

Here’s my sample file - I hope this was helpful.
eDNA - Resource Count.pbix (137.4 KB)

1 Like

Hi Melissa,

Thank you so much for replying to this query and you have done a great job. I did a similar solution to yours except, I had an active relationship between the date table and the start date and I did not have a measure table (I just had a measure within the resource data table). Anyway, your solution worked like magic so thanks again. Really appreciate it.

1 Like

:+1: Glad to help.