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 IDType of packageEmloyee RelativePhone numberStart dateEnd date1EMPLID000013StandartRelative 1 1-Jul-24 2EMPLID000018StandartRelative 2 1-Apr-23 3EMPLID000018StandartRelative 3 1-Jun-23 4EMPLID000020PlusRelative 4 1-Apr-241-Aug-245EMPLID000025PlusRelative 5 1-Aug-23 6EMPLID000032PlusRelative 6 1-May-23 7EMPLID000035PlusRelative 7 22-Aug-22 8EMPLID000054PlusRelative 8 1-Jul-24 9EMPLID000072StandartRelative 9 1-Jul-24 10EMPLID000075StandartRelative 10 7-Jan-24 11EMPLID000092PlusRelative 11 1-Oct-23 12EMPLID000098PlusRelative 12 1-Nov-22 13EMPLID000158PremiumRelative 13 1-Aug-231-Aug-2414EMPLID000194PlusRelative 14 5-Dec-231-Jun-2415EMPLID000202PlusRelative 15 13-Jun-241-Aug-2416EMPLID000215PlusRelative 16 1-Mar-23 17EMPLID000272StandartRelative 17 1-Mar-24 18EMPLID000282StandartRelative 18 1-Jun-241-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!

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! 😊😊😊

Anonymous

Not applicable

1 year ago

Solved

Dynamic Status Measure based on End date in table

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!

Need Help

Anonymous
1 year ago
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! 😊😊😊

Forum Discussion

Dynamic Status Measure based on End date in table

3 Replies

Related content

Dynamic measure filtering

Dynamic Measure that modifies status based on filters

Dynamic Measure developement

Dynamic Measure with Fieldparameters

Dynamic Filter