Forum Discussion
DAX: Date Difference required with New Measure
Hi,
Can you help me out with this query
One of my mate share dis DAX (Measure) for date diffarece. But i got some errors with query can you fix this
[ClosedDt-CreatedDt]
Daydiff = VAR Created = MAX('Dates'[CreatedDt])
VAR Closed = MAX('Dates'[ClosedDt])
VAR dd=-1
IF (Closed>Created) {
dd=DATEDIFF(Created;Closed;days)
} else {
dd= DATEDIFF(Closed;Created;days)
}
RETURN dd
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.
21 Replies
Try this:
Daydiff = VAR Created = MAX ( 'Dates'[CreatedDt] ) VAR Closed = MAX ( 'Dates'[ClosedDt] ) RETURN IF ( Closed > Created, DATEDIFF ( Created, Closed, DAY ), DATEDIFF ( Closed, Created, DAY ) )Note that I'm using commas to separate parameters, you may need to change back to semicolons.
I got rid of the "else", as that's not part of the IF() syntax.
Also note that the syntax for DATEDIFF() is DAY and not DAYS
Thanks Anonymous
It works great.
can you add the one more KPI to me in the below DAX
If noofdays is <= 21 "MetTarget, else "NotTarget
I will this one time, but I'm also going to teach you to do it yourself.
From MSDN, here is the syntax for the IF() function
IF( <logical_test>, <value_if_true>, value_if_false )matching your requirements to that pattern, and assuming "noofdays" is a measure:
[measure] = IF ( [noofdays] <= 21, "MetTarget", "NotTarget" )
