Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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
Solved! Go to 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.
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.
@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
)
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group