Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Compare two dates to create flag

Hello all,

I am looking at a solution to identify if there exists ML_date before Escalation_Date by each Serial number.

In the below table, for Serial_Number:100 , Escalation_Date is 3/2/2023 but the minimum ML_Date is 3/3/2023 which is after Escalation Date and hence "No" in the last coulmn.

For Serial_Number:200 , Escalation_Date is 4/1/2023 but the minimum ML_Date is 4/1/2023 which is on or before Escalation Date and hence "Yes". 

 

Serial_NumberEscalation_DateML_DateData_Available_On_Before_Escalation
1003/2/20233/5/2023No
1003/2/20233/4/2023No
1003/2/20233/3/2023No
2004/1/20234/2/2023Yes
2004/1/20234/1/2023Yes
3004/11/20234/8/2023Yes
3004/11/20234/7/2023Yes
3004/11/20234/6/2023Yes
4005/1/20235/4/2023No
4005/1/20235/3/2023No
4005/1/20235/2/2023No
2 ACCEPTED SOLUTIONS
DOLEARY85
Super User
Super User

Hi,

 

try a calculated column with this DAX:

 

Column = var _mindate = CALCULATE(min('Table'[ML_Date]),ALLEXCEPT('Table','Table'[Serial_Number]))

return

if ('Table'[Escalation_Date] < _mindate, "No", "Yes")
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

View solution in original post

serpiva64
Super User
Super User

Hi, 

you can add a calculated column

Column =
VAR currser = 'Table'[Serial_Number]

VAR minMLdate = CALCULATE( MIN('Table'[ML_Date]), FILTER('Table', 'Table'[Serial_Number]=currser ))
RETURN
IF( 'Table'[Escalation_Date] >= minMLdate, "yes", "no" )
 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

View solution in original post

3 REPLIES 3

Thanks @serpiva64 @DOLEARY85 both solutions have worked.

serpiva64
Super User
Super User

Hi, 

you can add a calculated column

Column =
VAR currser = 'Table'[Serial_Number]

VAR minMLdate = CALCULATE( MIN('Table'[ML_Date]), FILTER('Table', 'Table'[Serial_Number]=currser ))
RETURN
IF( 'Table'[Escalation_Date] >= minMLdate, "yes", "no" )
 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

DOLEARY85
Super User
Super User

Hi,

 

try a calculated column with this DAX:

 

Column = var _mindate = CALCULATE(min('Table'[ML_Date]),ALLEXCEPT('Table','Table'[Serial_Number]))

return

if ('Table'[Escalation_Date] < _mindate, "No", "Yes")
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.