Dynamic Status Measure based on End date in table

[orinoko0]

Hi dear community members,
I have a table as below,  and I need to crate measure which will genrate Status for every line of table based on Date Selected on dates slicers (comes from calendar table) and the End Date from lines of table given.

№	Employee ID	Type of package	Emloyee Relative	Phone number	Start date	End date
1	EMPLID000013	Standart	Relative 1		1-Jul-24
2	EMPLID000018	Standart	Relative 2		1-Apr-23
3	EMPLID000018	Standart	Relative 3		1-Jun-23
4	EMPLID000020	Plus	Relative 4		1-Apr-24	1-Aug-24
5	EMPLID000025	Plus	Relative 5		1-Aug-23
6	EMPLID000032	Plus	Relative 6		1-May-23
7	EMPLID000035	Plus	Relative 7		22-Aug-22
8	EMPLID000054	Plus	Relative 8		1-Jul-24
9	EMPLID000072	Standart	Relative 9		1-Jul-24
10	EMPLID000075	Standart	Relative 10		7-Jan-24
11	EMPLID000092	Plus	Relative 11		1-Oct-23
12	EMPLID000098	Plus	Relative 12		1-Nov-22
13	EMPLID000158	Premium	Relative 13		1-Aug-23	1-Aug-24
14	EMPLID000194	Plus	Relative 14		5-Dec-23	1-Jun-24
15	EMPLID000202	Plus	Relative 15		13-Jun-24	1-Aug-24
16	EMPLID000215	Plus	Relative 16		1-Mar-23
17	EMPLID000272	Standart	Relative 17		1-Mar-24
18	EMPLID000282	Standart	Relative 18		1-Jun-24	1-Aug-24

then the staus is "Open",
If Date Selected on slices >= that End date ,
then th status is "Closed"

I am going to use this measure for the card with drill-through, I have used the following calculation for the card to calculate dynamic number of active users

Fam Members Active = 
CALCULATE(DISTINCTCOUNT('Data'[Emloyee Relative]),
'Data'[End date]>max('My Calendar'[Date]) 
|| 
'Data'[End date]=blank())

and now want to add drill-through functionality to show members with active status only for the selected time period

as you see visual on first screenshot shows 13 elments, and drill-through page has all 18 elements

(Link to the PBI file )

Thank you in advance!

 

[orinoko0 · Accepted solution]

Seems to me that solution was very simple, I have just added the measure to drill-through table and in filters area made it equal to 1, it works now, not sure what I did differently earlier.
So it works!
Anyway thank you all for reading! 😊😊😊

[lbendlin]

Employee 18 is listed twice?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[orinoko0]

Post updated, thanks for the tips!