Forum Discussion
Solved
Date difference when condition met
Hi there,
I am calculating the date difference between two columns with a condition that if Date A column has '1/01/1900' then do not take the difference. Here is my code:
Diff_in_days =
Var retail_notify = MAX('Outages'[Date B])
Var interuption_start = MAX('Outages'[Date A])
Var test_con = FORMAT(retail_notify, "dd/mm/yyyy")
RETURN
CALCULATE( DATEDIFF(retail_notify,interuption_start,DAY), FILTER('Outages and Interruptions',test_con <> FORMAT(DATEVALUE("1/01/1900"),"dd/mm/yyyy"))
)
However, it does take the date difference when the Date A column has "1/01/1900". I used the format function because Date A and Date B column has a date/time format. Could anyone help me where am I making the mistake?
Please try this expression
Diff_in_days = VAR retail_notify = MAX ( 'Outages'[Date B] ) VAR interuption_start = MAX ( 'Outages'[Date A] ) RETURN IF ( YEAR ( retail_notify ) > 1900, DATEDIFF ( interuption_start, retail_notify, DAY ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
3 Replies
Please try this expression
Diff_in_days = VAR retail_notify = MAX ( 'Outages'[Date B] ) VAR interuption_start = MAX ( 'Outages'[Date A] ) RETURN IF ( YEAR ( retail_notify ) > 1900, DATEDIFF ( interuption_start, retail_notify, DAY ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Dunner2020 , Try like
Diff_in_days =
Var retail_notify = MAX('Outages'[Date B])
Var interuption_start = MAX('Outages'[Date A])
RETURN
CALCULATE( DATEDIFF(retail_notify,interuption_start,DAY), FILTER('Outages and Interruptions',not(isblank(retail_notify)) && retail_notify <> DATE(1900,01,01))
)
