Count total number of resources by Financial Year

DAX DAX Calculations
1

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:

Capture
Capture.PNG860×506 10.2 KB

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 =
CALCULATE (
COUNTROWS (
CALCULATETABLE ( ‘Resource Plan’, ‘Resource Plan’[Start Date] <= MaxDate, ALL ( ‘Calendar’[Date] ) )),
(ISBLANK ( ‘Resource Plan’[End Date]) || ‘Resource Plan’[End Date] > MaxDate))
RETURN
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )

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

2

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.

image
image1695×409 33.3 KB

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

image
image604×654 13.5 KB

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

Next Created the following measure:

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

With this result:
image

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
3

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
4

:+1: Glad to help.