Need help with loyal and reactivated DAX!

[awff]

Hello fellow PBI'ers

I'm trying to find the next latest date based on two columns. Basically, the Islost2 column should show the next LATEST start date greater than the previous end date and if next latest start date = end date , then return that start date. Essentially opposite of the function - EARLIER(). 

The results should be as below (simplified for this post):

Start Date	End date	Result
21-Dec-16	21-Dec-17	03-Jul-18
23-May-17	23-May-18	03-Jul-18
16-Oct-17	16-Oct-18	30-Nov-18
13-Nov-17	30-Nov-18	30-Nov-18
03-Jul-18	03-Jul-19	30-Nov-19
30-Nov-18	30-Nov-19	30-Nov-19
30-Nov-19	11-Jun-20	23-Feb-21
23-Feb-21	23-Feb-22	01-Apr-21
01-Apr-21	23-Feb-22

Below is the DAX used for "Islost2" which i know is incorrect as it is similar to the EARLIER() fuction; and hoping i could get some assistance tweaking it.

Islost2 = 
var Acc_Id = '_fact (Quote)'[AccountID]
var End_date = '_fact (Quote)'[End_Date__c]
var _current=
'_fact (Quote)'[End_Date__c]
var _next= 
CALCULATE(
    MAX('_fact (Quote)'[Start_Date__c]),
    FILTER(
        ALL('_fact (Quote)'),
        '_fact (Quote)'[QuoteStatus (invoiceRequested)] = "Invoiced" &&
        '_fact (Quote)'[AccountID] = Acc_Id &&
       '_fact (Quote)'[start_Date__c] < End_date))
return
_next

Hopefully this makes sense...

Thank you in advance!

[awff]

Hi @amitchandak 

Is there a way where if there is no longer a start date greater than the end date, instead of the blanks, it returns the latest end date? In this case 23-Feb-22 instead of blank?

This would allow me to use SWITCH(TRUE() function to determine lost customers is greater than 365 days between the end date and next start date or of the last end date is greater than today().

 

 

[amitchandak]

@awff , Based on what I got, Try like

Islost2 =
var Acc_Id = '_fact (Quote)'[AccountID]
var End_date = '_fact (Quote)'[End_Date__c]
var _current=
'_fact (Quote)'[End_Date__c]
var _next=
CALCULATE(
Min('_fact (Quote)'[Start_Date__c]),
FILTER(
ALL('_fact (Quote)'),
'_fact (Quote)'[QuoteStatus (invoiceRequested)] = "Invoiced" &&
'_fact (Quote)'[AccountID] = Acc_Id &&
'_fact (Quote)'[start_Date__c] > End_date))
return
_next

or

Islost2 =
var Acc_Id = '_fact (Quote)'[AccountID]
var End_date = '_fact (Quote)'[End_Date__c]
var _current=
'_fact (Quote)'[End_Date__c]
var _next=
CALCULATE(
Min('_fact (Quote)'[Start_Date__c]),
FILTER(
ALL('_fact (Quote)'),
'_fact (Quote)'[QuoteStatus (invoiceRequested)] = "Invoiced" &&
'_fact (Quote)'[AccountID] = Acc_Id &&
'_fact (Quote)'[start_Date__c] >= End_date))
return
_next

[awff]

Knew it was close! Helpful as always thank you.