Measure that returns count if conditions are met

[mangoman]

Hi,

I'm relatively new to PowerBI and can't wrap my head around getting measures so that I can create some visualisations.

I'm trying to get:

1. Number of employees terminated in the calendar year (01/01/20XX to 31/12/20XX) special case is current calendar year where it would be to today (15/11/2022 - update dynamically)

2. Number of employees employed at end of calendar year (31/12/20XX) or special case would be to date for current calendar year (15/11/2022 - update dynamically)

3. Number of employees hired during the year (01/01/20XX to 31/12/20XX) special case would be to date for current calendar year (15/11/2022 - update dynamically)

This will be the same format used in the future so I would like it to work 2023 year onwards as well.

I tried to use a date table, not sure how to upload pbix file and include data.
PBIX 
Excel to data

Expected results:

2022:
Hired 65
Fired 51
Currently employed = 46 (blanks for 2022)

2021:
Hired 55
Fired 45
Employed at end of year = 32 (for 2021)

[shreyamukkawar · Accepted solution]

Hi,

1. Create a table for year.

2. Create new column as 

Fired = if(SELECTEDVALUE('year table'[year])=YEAR(Sheet2[End Date]),COUNT(Sheet2[Id]))

as follows....

Best Regards,

Shreya Mukkawar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shreyamukkawar]

Hi Mangoman,

Please refer this solution.

For year 2022

Hired in 2022 = CALCULATE(COUNT(Sheet2[Id]),YEAR(Sheet2[Start date])=2022)

Hired in 2022 = CALCULATE(COUNT(Sheet2[Id]),YEAR(Sheet2[Start date])=2022)

Currently employed = CALCULATE(COUNTBLANK(Sheet2[End Date]))

For year 2021

Hired in 2021 = CALCULATE(COUNT(Sheet2[Id]),YEAR(Sheet2[Start date])=2021)

fired in 2021 = CALCULATE(COUNT(Sheet2[Id]),YEAR(Sheet2[End Date])=2021)

fired in 2021 = CALCULATE(COUNT(Sheet2[Id]),YEAR(Sheet2[End Date])=2021)

 

Best Regards,

Shreya Mukkawar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangoman]

Hi Shreya,

Thanks for all your help, I was considering using COUNT but I found this solution was static, is it possible to make the Measure more dynamic and robust for future years?

For example if this had to repeat for 2023 and 2024 when the data is updated can it be done? I feel like I've been overthinking the Measure.

For example if I had to use this for 2023 and 2024 without updating the measure every year is this possible?

Thanks