Count total number of resources by Financial Year

DAX DAX Calculations
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