Count people that appear in every financial year

[SuzieKidd]

Hi, I have a table of people with start and end dates, the start date is linked to my caldender table date and I need to be able to count the people for each financial year that the episode was open for.  I have added a column into my caldendar table to show the financial year and I'm using this as my filter, however it seems to only be counting those that started in a financial year and not counting them in subsequent years where it was still open.  I'm very new to Power BI and any help with this would be greatly appreciated.  Thank you

[BA_Pete]

Hi @SuzieKidd ,

Try an 'over time' measure like this:

_noofEmployees_ot =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    DISTINCTCOUNT(yourTable[ID]),
    FILTER(
        yourTable,
        yourTable[Start Date] <= __cDate
        && (__cDate < yourTable[End Date] || ISBLANK(yourTable[End Date]))
    )
)

Remove or disable the relationship you have between calendar[date] and yourTable[Start Date], then put this measure into a visual with calendar[FY] and it will give you the count of people present at the end of each year.

Pete

[SuzieKidd]

Thank you so much for this, although not giving me a count of all the people who appeared in the FY, including those that closed within the FY, this is very close to what I'm after.  Once again, thank you so much 🙂

[BA_Pete · Accepted solution]

No problem.

If you want the count of ANY people that featured during the year, not just thse still present at the end of the year, use this measure instead:

_noofEmployeesFeatured_ot =
VAR __maxDate = MAX(calendar[date])
VAR __minDate = MIN(calendar[date])
RETURN
CALCULATE(
    DISTINCTCOUNT(yourTable[ID]),
    FILTER(
        yourTable,
        yourTable[Start Date] <= __maxDate
        && (__minDate < yourTable[End Date] || ISBLANK(yourTable[End Date]))
    )
)

Pete

[SuzieKidd]

Pete, thank you so much, that works like a dream.  Have a great afternoon Suzie