Calculating whether record is the latest record in date range

[Anonymous]

Hello

I work for an organisation where operators submit returns giving their financial details.  Each return has a start and end date which is not the same for each operator.  Some operators submit quarterly or some submit annually.  I need to create a report that lists all the returns that fall within a certain reporting period (in the case 01/04/2017 to 31/03/2018) and I need to flag the latest return within that reporting period for each operator.  I am struggling to write this with DAX and have an added complication is that I'm using direct query due to the size of the tables and need to use measures rather than calculated columns.  Below is an example of the data and Islastreturnforreportingperiod? column is what I want to acheive.  I hope someone can help.  Thanks in advance Jo.

[PattemManohar]

@Anonymous  Thanks for the sample data. Please post the same in copiable format which will be helpful.

[Anonymous]

Hi @PattemManohar 

Thanks for your reply.  I've attached the dataset.

Thanks

Account NoOperator NameReturnNumberReporting_PeriodReturn StartdateReturn EnddateValueIslastreturnforreporting period?

2	Bob	1-134316591	01/04/2017-31/03/2018	01/01/2018	31/03/2018	113	Yes
2	Bob	1-125749521	01/04/2017-31/03/2018	01/07/2017	30/09/2017	97	No
3	Fred	1-115659651	01/04/2017-31/03/2018	01/04/2017	30/06/2017	39	No
3	Fred	1-119580041	01/04/2017-31/03/2018	01/07/2017	30/09/2017	86	Yes
4	Murphy	1-134316731	01/04/2017-31/03/2018	01/07/2017	30/09/2017	47	No
4	Murphy	1-125749661	01/04/2017-31/03/2018	01/10/2017	31/12/2017	32	Yes
5	Simon	1-115661261	01/04/2017-31/03/2018	01/04/2017	30/06/2017	61	No
5	Simon	1-134317671	01/04/2017-31/03/2018	01/07/2017	31/10/2017	116	Yes
6	Ted	1-122200421	01/04/2017-31/03/2018	25/07/2017	24/10/2017	38	No
6	Ted	1-122202331	01/04/2017-31/03/2018	25/01/2018	24/04/2018	86	Yes
7	Alan	1-119578651	01/04/2017-31/03/2018	01/04/2017	30/06/2017	7	No
7	Alan	1-127791801	01/04/2017-31/03/2018	01/07/2017	30/09/2017	19	Yes

[PattemManohar · Accepted solution]

@Anonymous  Please try this as a New Measure.

Test254Flag = 
VAR _MaxEnd = CALCULATE(MAX(Test254FlagLatest[EndDate]),ALLEXCEPT(Test254FlagLatest,Test254FlagLatest[AccountNo]))
RETURN IF(MAX(Test254FlagLatest[EndDate]) = _MaxEnd,"Yes","No")

[Anonymous]

thank you @PattemManohar  it works!

I've been puzzling over this for a long time.

Thanks again