Show data if end date is not reached

[Anonymous]

Hi guys,

I want to see which employees were working in my company during a filtered time period.

I tried using these measures:

    SelectedDate = 'Date'[Date]

    Active = IF(SelectedDate > MIN('Employee'[StartDate] && SelectedDate < 'Employee'[EndDate]; "y"; "n")

But this will always give "y" as a response

Let's say we'd look at 01-02-2020 then I'd want the Active- column to display the following:

Employee	StartDate	EndDate	Active?
employee a	1-1-2020		y
employee b	1-1-2016	31-06-2018	n
employee c	1-4-2018		y
employee d	1-12-2015		y
employee e	1-1-2018	31-12-2018	n
employee f	1-7-2017	31-12-2019	n
employee g	1-1-2019		y

[v-jayw-msft]

Hi @Anonymous ,

First you need to create a CALENDAR table and use date field as silcer.

CALENDAR = CALENDARAUTO()

Please refer to the measure below.

active = 
var startdate = SELECTEDVALUE('Table'[StartDate])
var enddate = SELECTEDVALUE('Table'[EndDate])
var mindate = MIN('CALENDAR'[Date])
var maxdate = MAX('CALENDAR'[Date])
return
IF((enddate<>BLANK()&&enddate<mindate)||startdate>maxdate,"n","y")

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[rainer1]

Hi @Anonymous,

you can try these Dax Code in a new calculated Column. This should work for you.

Active = 
var selectedDate = DATE(2020;02;01)

return
IF('Table'[EndDate] = BLANK() || 'Table'[EndDate] > selectedDate; "Y"; "N")

The output look like this.

Regards

-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

[Anonymous]

Hi @rainer1 ,

This works perfectly if I have one specific date, but I would like to be able to see this information for dates a user can pick.

Do you know how to solve this issue?

Kind regards

[rainer1 · Accepted solution]

Hi @Anonymous,

you can create a measure, replace Dates[Date] with your slicer Dates Table.

Active = 
SWITCH(
    TRUE();
    Max('Table'[EndDate]) = BLANK(); "Y";
    MAX(Dates[Date]) < MAX( 'Table'[EndDate]); "Y"; "N"
    )

 -------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

[amitchandak]

Refer My HR Blog: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

You can have an employee of every month or day