Counting Leavers using var _min and var _max and count rows

[drewmcspy]

Hi

I'm fairly new to PowerBI and need some help with a formula I have please.

Table Name: 'Staff Data'

What I'm trying to do it count the number of leavers on a staff list over a rolling 12 complete months period. Meaning, today is the 22nd February 2022 so if I refreshed the data it would count all rows of data which contained an leave date [Date Left] from 01/02/2021 (var _min) to 31/01/2022 (var _max). I also have a flag which determines whether each member of staff left voluntarily [Vol. Leaver].

The variables look correct to me so is there something incorrect with the way I am using the filter? 

Vol.LeaversLTM =
var _min = EDATE(EOMONTH(today(),-1),-12)+1
var _max = EOMONTH(today(),-1)
return COUNTROWS(CALCULATETABLE('Staff Data',FILTER('Staff Data',[Date Left] <= _max && 'Staff Data'[Date Left] >=_min),FILTER('Staff Data','Staff Data'[Vol. Leaver]= "yes")))

Thanks for any help you can give me.

[Icey · Accepted solution]

Hi @drewmcspy ,

Try this:

Vol.LeaversLTM =
VAR _min =
    EDATE ( EOMONTH ( TODAY (), -1 ), -12 ) + 1
VAR _max =
    EOMONTH ( TODAY (), -1 )
RETURN
    COUNTROWS (
        FILTER (
            ALL ( 'Staff Data' ),
            'Staff Data'[Date Left] <= _max
                && 'Staff Data'[Date Left] >= _min
                && 'Staff Data'[Vol. Leaver] = "yes"
        )
    )

Best Regards,

Icey

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

[tackytechtom]

Hi @drewmcspy,

Does it work with this?

Vol.LeaversLTM =
var _min = EDATE(EOMONTH(today(),-1),-12)+1
var _max = EOMONTH(today(),-1)
return
CALCULATE (
COUNTROWS ( 'Staff Data' ),
FILTER ('Staff Data', 'Staff Data'[Date Left] <= _max && 'Staff Data'[Date Left] >= _min && 'Staff Data'[Vol. Leaver] = "yes")
)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[drewmcspy]

Hi @tackytechtom 

Thanks for your reply. Unfortunately it doesn't It produces the same result as my original filter. The figure should be about 200 but I'm getting 32. I can't see how.

mcspy

[tackytechtom]

Hi @drewmcspy ,

Please can you share the data and the result you would like to achieve? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/