Forum Discussion

awff's avatar
awff
Helper III
3 years ago
Solved

Help! Return earliest date with condition in calculated column

Hi fellow PBI'ers

 

I've spend hours trying to work this out... I've trying to get the earliest instance of a date to calculate cumulative lifetime of a customer.

 

Basically, I need the earliest start date repeated until the start date is greater than 90 days from the last latest end date (defined as a churned customer).

 

Hopefully the "Desired result" column makes sense. Where there is "RESET" is where there is a 90 day or greater difference between the current row start date and the previous latest end date. In this instance, it would then use the new start date in the row

 

AccountStart_Date__cEnd_Date__cContract_daysDesired result 
Psuedo Company12-May-2019-May-20712-May-20 
Psuedo Company7-Feb-2225-Feb-22187-Feb-22RESET
Psuedo Company7-Feb-2225-Feb-22187-Feb-22 
Psuedo Company26-Feb-2222-Mar-22247-Feb-22 
Psuedo Company26-Feb-2222-Mar-22247-Feb-22 
Psuedo Company25-Mar-2225-Apr-22317-Feb-22 
Psuedo Company25-Mar-2225-Apr-22317-Feb-22 
Psuedo Company25-Apr-2225-May-22307-Feb-22 
Psuedo Company25-Apr-2225-May-22307-Feb-22 
Psuedo Company1-Oct-221-Nov-22311-Oct-22RESET
Psuedo Company1-Oct-221-Dec-22611-Oct-22 
Psuedo Company1-Dec-221-Jan-23311-Oct-22 

 

Here is what i have so far... 

Getting the previous end date. This is used to determine if if datediff between start date and this column is > 90 days.

 

 

 

Previous_end = 
var account = 'Table'[Account]
var previous_start = 'Table'[Start_Date__c]
var previous_end = 'Table'[End_Date__c]

var last_end_date = //find the last start date
CALCULATE(max('Table'[End_Date__c]),
    FILTER(
        ALL('Table'),
        'Table'[Account] = account &&
        'Table'[End_Date__c] < previous_end))

var fill_end = IF ( last_end_date = blank(), previous_end, last_end_date)

RETURN
fill_end

 

 

 

 Then i tried to find the start dates but it's just pretty much getting the previous rows start date...

 

 

 

earliest_start = 
var account = 'Table'[Account]
var previous_start = 'Table'[Start_Date__c]
var previous_end = 'Table'[End_Date__c]

var first_start_date = //find the last start date
CALCULATE(
    min('Table'[Start_Date__c]),
    FILTER(
        ALL('Table'),
        'Table'[Account] = account &&
        'Table'[Start_Date__c] >= previous_start &&
        datediff( 'Table'[Previous_end], 'Table'[Start_Date__c] , day) < 90 ))
RETURN
first_start_date

 

 

 

 

Any help would be appreciated and huge life saver!! 🙂

  • I think i've managed to tweak the first step DAX to get it to work! 

    Basically added an additional condition to get each latest previous end date per row and check to see if the current row start date is less than that.

     

    Jihwan_Kim thank you so much for your help! True lifesaver 🙂

     

    Step one CC = 
    var _starting = MIN('Data V3'[Start_Date__c])
    VAR _currentrowstartdate = 'Data V3'[Start_Date__c]
    VAR _currentrowenddate = 'Data V3'[End_Date__c]
    VAR _previousrowenddate =
        MAXX (
            FILTER ( 
                'Data V3', 'Data V3'[End_Date__c] <= _currentrowstartdate),
            'Data V3'[End_Date__c]
        )
    VAR _latestlastenddate = 
         MAXX (
            FILTER ( 
                'Data V3', 'Data V3'[End_Date__c] <= _currentrowenddate &&
                'Data V3'[Start_Date__c] < _currentrowstartdate),
            'Data V3'[End_Date__c]
        )
    VAR _diff =
        DATEDIFF ( _previousrowenddate, _currentrowstartdate, DAY )
    VAR _condition =
        IF ( 'Data V3'[Start_Date__c] = _starting || _diff > 90 && 'Data V3'[Start_Date__c] >= _latestlastenddate , 1, 0 )
    RETURN
        _condition

     

     

6 Replies

    • awff's avatar
      awff
      Helper III

      Hi Jihwan_Kim 

       

      Amazing! Thank you, it's so close! 

       

      I just applied this to a larger scale dataset and came across an issue where if there are dates that start and end on the same day, the desired result would appear incorrectly

       

      see image in the last row, reading 1/08/24 rather than 1/08/23. It seems to be common across contract days greater than 90

       

       

      below sample table with those dates:

       

      AccountStart_Date__cEnd_Date__cContract_daysDesired result 
      Psuedo Company12-May-2019-May-20712-May-20 
      Psuedo Company7-Feb-2225-Feb-22187-Feb-22RESET
      Psuedo Company7-Feb-2225-Feb-22187-Feb-22 
      Psuedo Company26-Feb-2222-Mar-22247-Feb-22 
      Psuedo Company26-Feb-2222-Mar-22247-Feb-22 
      Psuedo Company25-Mar-2225-Apr-22317-Feb-22 
      Psuedo Company25-Mar-2225-Apr-22317-Feb-22 
      Psuedo Company25-Apr-2225-May-22307-Feb-22 
      Psuedo Company25-Apr-2225-May-22307-Feb-22 
      Psuedo Company1-Oct-221-Nov-22311-Oct-22RESET
      Psuedo Company1-Oct-221-Dec-22611-Oct-22 
      Psuedo Company1-Dec-221-Jan-23311-Oct-22 
      Psuedo Company1-Aug-231-Aug-243651-Aug-23RESET
      Psuedo Company1-Aug-241-Aug-253651-Aug-23 
      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User

        Hi,

        Thank you for your feedback.

        Please check the below picture and the attached pbix file.

         

         

         

         

  • awff's avatar
    awff
    Helper III

    I think i've managed to tweak the first step DAX to get it to work! 

    Basically added an additional condition to get each latest previous end date per row and check to see if the current row start date is less than that.

     

    Jihwan_Kim thank you so much for your help! True lifesaver 🙂

     

    Step one CC = 
    var _starting = MIN('Data V3'[Start_Date__c])
    VAR _currentrowstartdate = 'Data V3'[Start_Date__c]
    VAR _currentrowenddate = 'Data V3'[End_Date__c]
    VAR _previousrowenddate =
        MAXX (
            FILTER ( 
                'Data V3', 'Data V3'[End_Date__c] <= _currentrowstartdate),
            'Data V3'[End_Date__c]
        )
    VAR _latestlastenddate = 
         MAXX (
            FILTER ( 
                'Data V3', 'Data V3'[End_Date__c] <= _currentrowenddate &&
                'Data V3'[Start_Date__c] < _currentrowstartdate),
            'Data V3'[End_Date__c]
        )
    VAR _diff =
        DATEDIFF ( _previousrowenddate, _currentrowstartdate, DAY )
    VAR _condition =
        IF ( 'Data V3'[Start_Date__c] = _starting || _diff > 90 && 'Data V3'[Start_Date__c] >= _latestlastenddate , 1, 0 )
    RETURN
        _condition