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.
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" )
Helper IIIThanks Anonymous
Thanks you so much for your time to resolve all my requests
I got my requirement. what im expecting is can we club these 2 queires into One DAX query
[measure] = IF ( [noofdays] <= 21, "MetTarget", "NotTarget" )
Daydiff =
VAR Created =
MAX ( 'Dates'[CreatedDt] )
VAR Closed =
MAX ( 'Dates'[ClosedDt] )
RETURN
IF (
Closed > Created,
DATEDIFF ( Created, Closed, DAY ),
DATEDIFF ( Closed, Created, DAY )
)
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.
- ssvr
Hi,
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