Measure to calculate count for same month previous year

[Anonymous]

I have the below fields in my table.

Employee ID, Start Date, End Date

I need to find the active employess present for previous year same month. (current month is Apr 2021, so the formula shoud take today as current date and give the filter for Apr 2020)

Start date <= Apr 30th 2020 AND (End date >= Apr 1st 2020 OR End date is Blank)

Please help

[v-yalanwu-msft · Accepted solution]

Hi @Anonymous  ,

You could create a measure by the following formula：

count =
COUNTROWS (
    SUMMARIZE (
        FILTER (
            'Table',
            EOMONTH ( [Start date], 0 ) <= EOMONTH ( TODAY (), -12 )
                && (
                    EOMONTH ( [End date], 0 ) >= EOMONTH ( TODAY (), -12 )
                        || [End date] = BLANK ()
                )
        ),
        'Table'[Name]
    )
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

You can try the following measure to get the count:

Emp Count = 
VAR MinDate =
    EOMONTH(TODAY(),-13) + 1
VAR MaxDate =
    EOMONTH(TODAY(),-12)
RETURN
COUNTROWS(    
    FILTER(
        Table,
        EndDate=BLANK() OR 
        (
            EndDate >= MinDate &&
            StartDate <= MaxDate
        )
    )
)