Forum Discussion
Helper IIIDAX: Date Difference required with New Measure
Daydiff = VAR Created = MAX ( 'Dates'[CreatedDt] ) VAR Closed = MAX ( 'Dates'[ClosedDt] ) VAR NoOfDays = IF ( Closed > Created, DATEDIFF ( Created, Closed, DAY ), DATEDIFF ( Closed, Created, DAY ) ) RETURN IF ( NoOfDays >= 21, "Met SLA", "NotMet SLA" )Just created another variable called NoOfDays with the calculation you wanted, then use it in the other if statement
ssvr, this should do it
DDiff_DueDateCloDate = VAR Due = INT( MAX ( 'Task'[DueDate] ) ) VAR Clos = INT( MAX ( 'Task'[ClosedDate] ) ) RETURN Due - Clos
INT() will convert a date into an integer.
Then you simply subtract one number from the other. This will make the result show a negative number, and should be the fastest performance-wise.
ssvr, it is feasible to combine these, yes. However, I don't understand what the goal of your calculation is.
Can you write out the business logic of what you are trying to do? Don't even worry about writing DAX, just some IF...THEN logic of what you want to check, and what you want to calculate based on the result.
I don't see how the [noofdays] measure ties into the other one. I'm sure it does, but need a little more direction.
Helper IIIHi,
My requirement is
= ClosedDt - ReleaseDt = NoOfDays, If the noofdays >= 21 daysSLA, That should be updated as "Met SLA" if not "NotMet SLA"
Daydiff = VAR Created = MAX ( 'Dates'[CreatedDt] ) VAR Closed = MAX ( 'Dates'[ClosedDt] ) VAR NoOfDays = IF ( Closed > Created, DATEDIFF ( Created, Closed, DAY ), DATEDIFF ( Closed, Created, DAY ) ) RETURN IF ( NoOfDays >= 21, "Met SLA", "NotMet SLA" )Just created another variable called NoOfDays with the calculation you wanted, then use it in the other if statement
- ssvr
Hi Anonymous
One more time, I need your help (Some change required in below DAX)
DDiff_RelDateCloDate = VAR Release = MAX ( 'Task'[ReleaseDate] ) VAR Closed = MAX ( 'Task'[ClosedDate] )
RETURN IF ( Closed > Release, DATEDIFF ( Release, Closed, DAY ), DATEDIFF ( Closed, Release, DAY ) )Note: Sometimes [ReleaseDate] field is nodate : [ClosedDate] field is with date
[ReleaseDate] field is with date : [ClosedDate] field is no date
In that case DDiff_RelDateCloDate field need to be updated as "ReleaseDate is blank" / "ClosedDate is blank"
I can say thank you so much in advance!
Try this:
Daydiff = VAR Release = MAX ( 'Task'[ReleaseDate] ) VAR Closed = MAX ( 'Task'[ClosedDate] ) VAR EarliestDate = MIN ( Release, Closed ) VAR LatestDate = MAX ( Release, Closed ) VAR NoOfDays = DATEDIFF ( EarliestDate, LatestDate, DAY ) RETURN SWITCH ( TRUE (), Release = BLANK (), "ReleaseDate is blank", Closed = BLANK (), "ClosedDate is blank", NoOfDays >= 21, "Met SLA", "NotMet SLA" )A couple new variables. EarliestDate is the smaller of the two variables Release and Closed. LatestDate is the bigger of the two.
This lets you avoid the IF() statement by always putting the earlier date first in the DATEDIFF() function.
SWITCH( TRUE().... ) is a pretty well known pattern (you can find articles on it all over). It basically is an easier to read nested If statement.
It says "if Release is blank then say 'ReleaseDate is blank'...else if Closed is blank then say 'CloseDate is blank'...else check if NoOfDays >=21., etc."
Hope this helps
- ssvr
Hi Anonymous Master,
Hope you are doing good.
The below provided Date Diffarence DAX is working fine.
DDiff_DueDateCloDate = VAR Due = MAX ( 'Task'[DueDate] ) VAR Clos = MAX ( 'Task'[ClosedDate] )
RETURN IF ( Clos > Due, DATEDIFF ( Due, Clos, DAY ), DATEDIFF ( Clos, Due, DAY ) )I need some change from the Output
Example about DueDate & ClosedDate.
ClosedDate DueDate Diffarence 9/20/2018 9/26/2018 6 9/29/2018 9/26/2018 -3 -- If ClosedDate is lessthan the DueDate output noofdays (+Positive number)
-- If ClosedDate is Greatherthan the DueDate output noofdays (-Negative number)
Please help me out this Master :)
ssvr, this should do it
DDiff_DueDateCloDate = VAR Due = INT( MAX ( 'Task'[DueDate] ) ) VAR Clos = INT( MAX ( 'Task'[ClosedDate] ) ) RETURN Due - Clos
INT() will convert a date into an integer.
Then you simply subtract one number from the other. This will make the result show a negative number, and should be the fastest performance-wise.