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.
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
Helper IIIAnonymous
Thanks for your time and support
Its not working correctly ! i enclosed some screen shots.
In Daydiff measure output : i need daysdiffarence, releasedate blank, closedate blank (only these 3 outputs required)
- I will make another measure for >= 21 SLA "Met SLA" "NotMet SLA"
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 )Not sure why "ReleaseDate is blank" is showing up in those rows. Do you have any other filter context going on?
That updated measure will just output those 3 values. You can display SLA elsewhere.
- ssvr
Anonymous Thanks
No filters applied
Might be some DAX error
If you get any accurate DAX please share with me
- ssvr
Anonymous
I posted this requirement on few days back no one replied
Please look into it if you have time
Day Diffarence excluding weekends
I created a Date table:
Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))
I created a new column with is working day or not
is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)
Now I want to create "DateDifference" Column with Createddate & Closeddate (I want to know day diffarence b/w these two dates excluding weekends)
CreatedDate ClosedDate DateDiffarence
send me the link to that thread. That way others can search for the thread and see the solution