Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Account | Start_Date__c | End_Date__c | Contract_days | Desired result | |
Psuedo Company | 12-May-20 | 19-May-20 | 7 | 12-May-20 | |
Psuedo Company | 7-Feb-22 | 25-Feb-22 | 18 | 7-Feb-22 | RESET |
Psuedo Company | 7-Feb-22 | 25-Feb-22 | 18 | 7-Feb-22 | |
Psuedo Company | 26-Feb-22 | 22-Mar-22 | 24 | 7-Feb-22 | |
Psuedo Company | 26-Feb-22 | 22-Mar-22 | 24 | 7-Feb-22 | |
Psuedo Company | 25-Mar-22 | 25-Apr-22 | 31 | 7-Feb-22 | |
Psuedo Company | 25-Mar-22 | 25-Apr-22 | 31 | 7-Feb-22 | |
Psuedo Company | 25-Apr-22 | 25-May-22 | 30 | 7-Feb-22 | |
Psuedo Company | 25-Apr-22 | 25-May-22 | 30 | 7-Feb-22 | |
Psuedo Company | 1-Oct-22 | 1-Nov-22 | 31 | 1-Oct-22 | RESET |
Psuedo Company | 1-Oct-22 | 1-Dec-22 | 61 | 1-Oct-22 | |
Psuedo Company | 1-Dec-22 | 1-Jan-23 | 31 | 1-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!! 🙂
Solved! Go to Solution.
Hi,
Thank you for your feedback.
Please check the below picture and the attached pbix file.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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
Hi,
Please check the below picture and the attached pbix file.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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:
Account | Start_Date__c | End_Date__c | Contract_days | Desired result | |
Psuedo Company | 12-May-20 | 19-May-20 | 7 | 12-May-20 | |
Psuedo Company | 7-Feb-22 | 25-Feb-22 | 18 | 7-Feb-22 | RESET |
Psuedo Company | 7-Feb-22 | 25-Feb-22 | 18 | 7-Feb-22 | |
Psuedo Company | 26-Feb-22 | 22-Mar-22 | 24 | 7-Feb-22 | |
Psuedo Company | 26-Feb-22 | 22-Mar-22 | 24 | 7-Feb-22 | |
Psuedo Company | 25-Mar-22 | 25-Apr-22 | 31 | 7-Feb-22 | |
Psuedo Company | 25-Mar-22 | 25-Apr-22 | 31 | 7-Feb-22 | |
Psuedo Company | 25-Apr-22 | 25-May-22 | 30 | 7-Feb-22 | |
Psuedo Company | 25-Apr-22 | 25-May-22 | 30 | 7-Feb-22 | |
Psuedo Company | 1-Oct-22 | 1-Nov-22 | 31 | 1-Oct-22 | RESET |
Psuedo Company | 1-Oct-22 | 1-Dec-22 | 61 | 1-Oct-22 | |
Psuedo Company | 1-Dec-22 | 1-Jan-23 | 31 | 1-Oct-22 | |
Psuedo Company | 1-Aug-23 | 1-Aug-24 | 365 | 1-Aug-23 | RESET |
Psuedo Company | 1-Aug-24 | 1-Aug-25 | 365 | 1-Aug-23 |
Hi,
Thank you for your feedback.
Please check the below picture and the attached pbix file.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks @Jihwan_Kim
Again, thank you for your help with this. I've been pulling my hairs over this in the past few days! Don't want to sound like i'm taking this for granted but I really appreciate your helping on this!
So just when PBI gets your hopes up, i've come across another situation where the highligted dates should be under the same date group as "17-Feb-22". This happens when a customer extends their contracts or purchases additional modules to their exsiting contract.
I hope this isn't confusing but i think this should be the last of it!
In this case, since 19-Sep-22 start date in the highlighted row start is between the existing latest 23-Aug-23 contract, the desired result should still be 17-Feb-22.
Strange as the first few rows have similar pattern but the results are correct... I think there needs to be an additional parameter like
if start date > latest last end date && latest end date and is not > 90 days then get _previousrowenddate
Apologies, i don't know how to upload PBI file here.
Dataset:
Account | Start_Date__c | End_Date__c | Contract_days | Desired result | |
Psuedo Company | 17-Feb-21 | 17-Feb-22 | 7 | 17-Feb-21 | |
Psuedo Company | 10-Mar-21 | 10-Mar-22 | 18 | 17-Feb-21 | |
Psuedo Company | 25-May-21 | 25-May-22 | 18 | 17-Feb-21 | |
Psuedo Company | 19-May-22 | 19-May-23 | 24 | 17-Feb-21 | |
Psuedo Company | 21-Jun-22 | 21-Jun-23 | 24 | 17-Feb-21 | |
Psuedo Company | 20-Jul-22 | 20-Jul-23 | 31 | 17-Feb-21 | |
Psuedo Company | 23-Aug-22 | 23-Aug-23 | 31 | 17-Feb-21 | |
Psuedo Company | 23-Aug-22 | 23-Aug-23 | 30 | 17-Feb-21 | |
Psuedo Company | 19-Sep-22 | 19-Sep-23 | 30 | 17-Feb-21 | |
Psuedo Company | 19-Sep-22 | 19-Sep-23 | 31 | 17-Feb-21 | |
Psuedo Company | 19-Oct-22 | 19-Oct-23 | 61 | 17-Feb-21 | |
Psuedo Company | 19-Oct-22 | 19-Oct-23 | 31 | 17-Feb-21 | |
Psuedo Company | 19-Oct-22 | 19-Oct-23 | 365 | 17-Feb-21 | |
Psuedo Company | 01-Aug-24 | 01-Aug-25 | 365 | 01-Aug-24 | RESET |
^ sorry ignore the contract days