Measure to calculate the latest dayid for each record which is not greater than the filter selected

[Anonymous]

Dear Community, 

I'm currently facing a challenge in resolving the following issue.
1. I need to find the max (day_id) for each record based on user slicer in Power BI. 
2.  then count the  distinct subscriptionid's with respect to status on above criteria

My data looks something like this:

Table1:

Subscription_id	Day_id	Status
123456	20200101	Active
123457	20200105	Cancel
123456	20200118	Frozen
123457	20200203	Frozen
123789	20200208	Active
123456	20200228	Active
123478	20200301	Frozen
123780	20200210	Active

I'm trying to acheive some thing like this:

if user selects day_id as 20200115 (jan -15-2020), it should display below records

Subscription_id	Day_id	Status	Max_day_id
123456	20200101	Active	20200101
123457	20200105	Cancel	20200105

Count of active -1;

Count of Cancel -1;

if user selects day_id as 20200130 (jan -30-2020), it should display below records

Subscription_id	Day_id	Status	Max_day_id
123456	20200101	Active	20200118
123457	20200105	Cancel	20200105
123456	20200118	Frozen	20200118

Count of active -0;

Count of Cancel -1;
count of Frozen - 1;

if user selects day_id as 20200211 (Feb-11-2020), it should display below records

Subscription_id	Day_id	Status	Max_day_id
123456	20200101	Active	20200118
123457	20200105	Cancel	20200203
123456	20200118	Frozen	20200118
123457	20200203	Frozen	20200203
123789	20200208	Active	20200208
123780	20200210	Active	20200210

Count of active -2;

Count of Cancel -0;
count of Frozen - 2;

It's surely silly, but please help!!

[amitchandak]

@Anonymous , Create a measure like this and use

Measure =
VAR __id = MAX ( 'Table'[Subscription_id] )
VAR __date = CALCULATE ( MAX( 'Table'[Day_id] ), ALLSELECTED ( 'Table' ), 'Table'[Subscription_id] = __id )
RETURN CALCULATE ( Max ( 'Table'[Status] ), VALUES ( 'Table'[Subscription_id] ), 'Table'[Subscription_id] = __id, 'Table'[Day_id] = __date )

[Anonymous]

Hey @amitchandak , 

Thanks for you quick response. It works as expected.

Just as an add on, can we get this measure to show only the latest records instead of showing whole data? 

[amitchandak]

@Anonymous , if only one record

Measure =
VAR __id = MAX ( 'Table'[Subscription_id] )
RETURN CALCULATE ( Max ( 'Table'[Status] ), 'Table'[Day_id] = __date )

[Anonymous]

Awesome. Thanks!