How to use Multiple if and Difference between 2 days

[Anonymous]

Hi  everyone i have to solve this problem and i dont' konw how to solve         

  I have 2 dax calculation issues

1. How to use multiple if condition

if finalstatus=correct

reqcloseddate<=reqdueday true means "Y" false "N"

else
currentday<=requestduedate true "Y" false "N"

I have Tried

Column =
SWITCH(TRUE(),
SUM(Overall[FinalStatus])=="correct",
SUM(Overall[RequestClosedDate])<=SUM(Overall[RequestDueDate]),"yes","No",
TODAY()<=SUM(Overall[RequestClosedDate]),"yes","No")

2. how to find difference between two dates including days hours (only weekdays) and (unique ID only calculate duplicate ID means return 0 )

Difference between twodays concider only Weekdays (exclude saturday sunday)

i have used

DATEDIFF(table[Date1], table[Date2], DAY),0)

but i need result like this

Date1                         Date2                         ID                   result
12/06/2010 12:01    15/06/2020 01:05          1                   1 01:04
14/06/2010 12:01    15/06/2020 01:05 2       1                    01:04
12/06/2010 12:01    12/06/2020 06:05          2                    0
15/06/2010 12:01    15/06/2020 06:05          4                   1 05:04
14/06/2010 12:01    15/06/2020 01:05          5                   1 01:04

Thanks so much in advace for your help, i really appreciate it 

Regards,

Rajesh

[harshnathani · Accepted solution]

HI @Anonymous ,

1. Create a Column.

Column =
SWITCH (
    TRUE (),
    Overall[FinalStatus] = "correct"
        && Overall[RequestClosedDate] <= Overall[RequestDueDate], "yes",
    Overall[FinalStatus] = "correct"
        && TODAY () <= Overall[RequestClosedDate], "yes",
    "No"
)

2. To count only working days --> https://www.sqlbi.com/articles/counting-working-days-in-dax/

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani    

its woiking fine   Column =
SWITCH (
    TRUE (),
    Overall[FinalStatus] = "correct"
        && Overall[RequestClosedDate] <= Overall[RequestDueDate], "yes",
    Overall[FinalStatus] = "correct"
        && TODAY () <= Overall[RequestClosedDate], "yes",
    "No"
)

I Need Distinct count of Ticketno   how to use this

Condition :       Today< Due date , count of Unique Ticket ID

i'm using 

IF ( TODAY ()+3 < DATE ( YEAR ( Table[DueDate]), MONTH ( Table[DueDate] ), DAY ( Table[DueDate] ) ), DISTINCTCOUNT(Table[Ticketno]), 0 )

[harshnathani]

Hi @Anonymous ,

I think you are asking too many question in one message.

Would request you to pls close this mesage and raise a new request .

This will be useful to the participants in the forun who look for answer pertaining to a particular area.

Regards,

Harsh Nathani

[ChrisGec]

Hi,

is this for a DAX column?

If yes you could use

you could use

IF(AND(EARLIER(Overall[FinalStatus]) = "correct", EARLIER(Overall[RequestClosedDate])<=EARLIER(Overall[RequestDueDate])), "Y" , IF(EARLIER(Overall[RequestDueDate])> TODAY(), "Y", "N"))

(This is not tested)

CONCATENATE(DATEDIFF(table[Date1], table[Date2], Minute),CONCATENATE(DATEDIFF(table[Date1], table[Date2], Hour), CONCATENATE(DATEDIFF(table[Date1], table[Date2], DAY), " "),":"),"")

(This is also not tested)