Forum Discussion

ssvr's avatar
ssvr
Icon for Helper III rankHelper III
8 years ago
Solved

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 Cr...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago
    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

  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • ssvr's avatar
    ssvr
    Icon for Helper III rankHelper III
    8 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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" 
)
      • ssvr's avatar
        ssvr
        Icon for Helper III rankHelper III
        8 years ago
        Thanks I got dis

        But I want to incorporate this one into previous DAX query (Daydiff)