Evolution of the number of employees over 5 years

[florentbignier]

Hello,

I have an employee table with a start date column and an end date column.

I also have a "Year" segment that allows me to filter the report by year.

I would like to have, over the last 5 years of the filter, the evolution of the number of employees of my company.

 Can we do this under PowerBI?

 Sample data:

ID, StartDate, EndDate

1,2011-01-01, null

2,2012-02-07,2014-01-21
3,2013-03-14,2016-07-11
4,2014-04-23, null
5,2015-05-05,2015-07-14
6,2016-06-09,2019-12-12
7,2017-07-30, null
8,2018-08-21, null
9,2019-09-15, null
10,2020-10-16, null

Expected results :

When Filter on 2015:

- 2011: 1

- 2012: 2

- 2013: 3

- 2014: 4

- 2015: 4

When filter on 2020:

- 2016: 4

- 2017: 4

- 2018: 5

- 2019: 6

- 2020: 6

Thanks in advance,

-Florent

[v-jiascu-msft · Accepted solution]

Hi Florent,

Please download a demo from the attachment. Is the "Year" in another table? If not, you may need a new table.

Table =
FILTER (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( 'Table1', "Year", YEAR ( [StartDate] ) ),
            SELECTCOLUMNS ( 'Table1', "Year", YEAR ( [EndDate] ) )
        )
    ),
    ISBLANK ( [Year] ) = FALSE ()
)

Create a measure.

Measure =
VAR selectedYear =
    MIN ( 'Table'[Year] )
VAR rowYear =
    YEAR ( MIN ( 'Table1'[StartDate] ) )
RETURN
    CALCULATE (
        IF (
            rowYear
                > selectedYear - 5
                && rowYear <= selectedYear,
            COUNTX (
                FILTER (
                    ALL ( 'Table1' ),
                    YEAR ( [StartDate] ) <= rowYear
                        && YEAR ( IF ( ISBLANK ( [EndDate] ), DATE ( 9999, 12, 31 ), [EndDate] ) )
                            >= rowYear
                ),
                [ID]
            ),
            BLANK ()
        )
    )

Best Regards,

[florentbignier]

Thank you !

Your 2 solutions work, but v_jiascu_msft's is a little faster 🙂

Regards,

Florent

[Greg_Deckler]

This looks like a date interval sort of situation, I created a couple of Quick Measures to deal with this sort of thing:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365