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.

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

Thanks 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 )
    )

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    ssvr
    Icon for Helper III rankHelper III
    8 years ago

    Hi,

     

    My requirement is

     

    = ClosedDt - ReleaseDt = NoOfDays, If the noofdays >= 21 daysSLA, That should be updated as "Met SLA" if not "NotMet SLA"

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

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

        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!