Employee Start of FY

[corange]

Hi Power BI Team, 

Could someone help me fix my below formula so it will count the number of employee at the start of a new FY: 

EmployeeStartYear =

VAR MaxDate = STARTOFYEAR('Calendar'[Date])

VAR EMPCOUNT =

CALCULATE(COUNTROWS(CALCULATETABLE(Drivers, Drivers[StartDate] <= MaxDate, ALL ('Calendar'[Date]))),

(ISBLANK(Drivers[FinishDate])|| Drivers[FinishDate] > MaxDate))

RETURN IF(ISBLANK(EMPCOUNT), 0, EMPCOUNT)

With the above formula, I obtained the following value for EmployeeStartYear. The number should be the same throughout the entire financial year. However, it changes in January in both instance which tells me it is not correct. Thank you. 

[amitchandak]

@corange , You can use startofyear in your calendar and compare it with that.

If you want to repeat then you need to have it enddate and you can use CLOSINGBALANCEYEAR , there is OPENINGBALANCEYear, but that also work on the last date of last year

The option is firstnonblankvalue too

I posted 2 video's on that

https://www.facebook.com/watch/?v=295771268202058

https://www.facebook.com/watch/?v=343509629992272

[Mariusz]

Hi @corange 

You can use the second paramiter in STARTOFYEAR function to set the start of financial Year

https://dax.guide/startofyear/ 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[corange]

Hi Mariusz, 

I am still a beginner in the area so was wondering if you could show me exactly how this would look like in my formula? 

Also, what if there is more than one FY, how do I set up the date? 

Thank you. 

[v-yiruan-msft]

Hi @corange ,

You can refer the following blog to adjust the calendar table. If it still not working, please provide some original sample data and your expected result in order to provide you solution suitable for your scenario. Thank you.

Financial Year Calculation In Excel and Power BI

Best Regards

Rena

[corange]

Hi @v-yiruan-msft, 

Please find my sample via the following link: https://1drv.ms/u/s!AiS7XVNuQsBCcllNYN9yYB8RBEM?e=88sub9 

I am trying to calculate the turnover rate for the FY. I am based in Australia so it goes from July to June and the calculation will be use across multiple financial year. I refered to the article you have posted and I am prety sure I have used it in the past to help me. 

What is happening is that the calculation EmployeeStartYear should be the same across the entire FY - e.g. 2019 - 2020 - 189 employees. However, as we go through the year, it always update when we hit January which then mean that my yearly average to calculatge the turnover isnt accurate. 

Thanks in advance for your help. 

[v-yiruan-msft · Accepted solution]

Hi @corange ,

I updated the formula of measure "EmployeeStartYear" and "Employee Count EndofYear", please check whether they are what you want. You can check the details in the updated report file.

EmployeeStartYear =

VAR MaxDate = CALCULATE(MIN('Calendar'[Date]),ALLEXCEPT('Calendar','Calendar'[FY]))//STARTOFYEAR('Calendar'[Date])

VAR EMPCOUNT =

CALCULATE(COUNTROWS(CALCULATETABLE('Sample', 'Sample'[StartDate] <= MaxDate, ALL ('Calendar'[Date]))),

(ISBLANK('Sample'[FinishDate])|| 'Sample'[FinishDate] > MaxDate))

RETURN IF(ISBLANK(EMPCOUNT), 0, EMPCOUNT)

Employee Count EndofYear =

VAR MaxDate = CALCULATE(MAX('Calendar'[Date]),ALLEXCEPT('Calendar','Calendar'[FY]))//ENDOFYEAR('Calendar'[Date])

VAR EMPCOUNT =

CALCULATE(COUNTROWS(CALCULATETABLE('Sample', 'Sample'[StartDate] <= MaxDate, ALL ('Calendar'[Date]))),

(ISBLANK('Sample'[FinishDate])|| 'Sample'[FinishDate] > MaxDate))

RETURN IF(ISBLANK(EMPCOUNT), 0, EMPCOUNT)

Best Regards

Rena

[corange]

Hi @v-yiruan-msft , 

This worked perfectly. Thank you.