Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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().
@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
Knew it was close! Helpful as always thank you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |