DAX: Date Difference required with New Measure

[ssvr]

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

[Anonymous · Accepted solution]

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]

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]

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]

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]

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]

@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"

[Anonymous · Accepted solution]

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]

Hi @Anonymous Master,

Hope you are doing good.

The below provided Date Diffarence DAX is working fine.

DDiff_DueDateCloDate = VAR Due = MAX ( 'Task'[DueDate] ) VAR Clos = MAX ( 'Task'[ClosedDate] )
RETURN IF ( Clos > Due, DATEDIFF ( Due, Clos, DAY ), DATEDIFF ( Clos, Due, DAY ) )

I need some change from the Output

Example about DueDate & ClosedDate.

ClosedDate	DueDate	Diffarence
9/20/2018	9/26/2018	6
9/29/2018	9/26/2018	-3

-- If ClosedDate is lessthan the DueDate output noofdays (+Positive number)

-- If ClosedDate is Greatherthan the DueDate output noofdays (-Negative number)

Please help me out this Master 🙂

[Anonymous · Accepted solution]

@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]

Thank you so much @Anonymous DAX Master 🙂

[ssvr]

Hi @Anonymous

I have one doubt

I want to create a Date Hirachy for one of the existing date columns/measure

I will enclosed the example screen shot for your reference

Please help me out

[ssvr]

Hi @Anonymous,

One doubt from my end. Its a new one 🙂

While applying below DAX i got one error.,

truecondiation = IF(ISM[OData_{CH} Recommend0]=BLANK(),ISM[OData_{RNSP} TSS_x00],ISM[OData_{CH} Recommend0])

Expressions that yield variant data-type cannot be used to define calculated columns.

Can you fix this for me.

[ssvr]

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!

[Anonymous]

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

[ssvr]

@Anonymous

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"

[Anonymous]

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

[Anonymous]

send me the link to that thread.  That way others can search for the thread and see the solution

[ssvr]

https://community.powerbi.com/t5/Desktop/Date-Difference-excluding-weekends/m-p/434143#M200154