Calculating FTE Turnover (Resigned date problems)

[Desauv]

Hi

I'm trying to calculated FTE turnover, which giving me some headaches. I know the formula for the Turnover Rate, but I'm having a hard time calculating the correct number resigned FTE in the selected Period.

So below example it shows that there is 5 resigned FTE, but there should only be 2. Let me explain.

I know that below calculation sum all the resigned FTE from 01-06-2022 and forward. But I want it to only sum all the resigned FTE in the period from 01-06-2022 -> 30-06-2022 (Based on the selected date). And if i had chosen two dates (01-06-22 and 01-07-22) I want it to sum from 01-06-22 -> 31-07-2022. 

I've tried so many things, but nothing gives me the right answer 😕

Hope someone can help, thanks 🙂

FTE_Resigned = 
    CALCULATE(SUMX(DISTINCT('HR data'[EMPL_NO]),'HR data'[FTE_Average]),

        FILTER(VALUES('HR data'[Resigneddate]), AND('HR data'[Resigneddate]>=MIN([Date].[Date]), 'HR data nyeste'[Resigneddate]<> BLANK())))

[Desauv · Accepted solution]

Hi Alisa

Actually I just found a solution for my problem.

FTE_Resigned_4 = 

SUMX(
    FILTER(    
        FILTER('HR data ', 'HR data'[ResignedDate] <> BLANK()),
        AND('HR data'[ResignedDate] <= EOMONTH('HR data'[Date].[Date],0),'HR data'[ResignedDate] >= EOMONTH('HR data'[Dato].[Date],-1)+1)
        )
, 'HR data'[FTE])

[Anonymous]

Hi @Desauv 

Can you provide your data for me ? There is nothing wrong with the formula alone . If possible, you can provide your pbix file(remove sensitive info) to us so that we can deal with your problem easier .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.