calculate time with conditions?

[Jolyon]

Hello all,

need your help please.

I have a table with tickets and different status.

Now I would need to make a new column and calculate the Pending Time for each ticket, that is not closed or cancelled.

i.e.  I search for a formula like:

if (status = "open" or "pending" or "in progress"), then Pending time = (today) - (Open date).

How can I also render this Pending Time in days, not hours?

Thanks a lot!

[nickneck]

Hi,

I would use this formula to add a custom column:

= Table.AddColumn(#"Changed Type", "Pending Days", each 
if [Status]="open" or [Status]="pending" or [Status]="in progress" 
then Duration.Days(Date.From(DateTime.LocalNow()) - [Open date]) 
else "")

[PavelR]

@Jolyon

I would recommend this formula:

Pending time = IF(AND(Tickets[Status] <> "Closed";Tickets[Status] <> "Cancelled");DATEDIFF(Tickets[Open date];TODAY();DAY);BLANK())

Regards.

Pavel

[Jolyon]

Hi @PavelR,

thanks for the formula,it seems to work,but I have a question. Can this formula work with three arguments?

I have noticed now, I have 3 Stata,that I have to include in if-condition - closed, cancelled and resolved.

In this case Power BI gives me an error,saying,that IF(AND) condition can work with only 2 arguments.

Or alternatively could I represent the formula with "positive" condition like below?

Pending days = IF(Tickets[Status] = "in progress" or Tickets[Status] = " Pending" or Tickets[Status] =  "Open") DATEDIFF(Tickets[Open Date];TODAY();DAY);BLANK())

[PavelR]

@Jolyon

No problem, lets use this formula:

Pending time = IF(Tickets[Status] <> "Closed" && Tickets[Status] <> "Cancelled" && Tickets[Status] <> "Resolved";DATEDIFF(Tickets[Open date];TODAY();DAY);BLANK())

Simply replace AND function with logical and - &&

Regards.

Pavel

[Jolyon]

@PavelR I have two more questions,if I may.

1) In formula DATEDIFF(Tickets[Open date];TODAY();DAY);BLANK())  which format does TODAY have?                         

I mean if we speak about today(13.01.2017), does Power BI take it as 13.01.2017 00:00:01? Because I worked today with old data and the formula worked very well. But when I refreshed then my data,I got an error saying, that the starting date can not be older then end date.

I suppose there could be a ticket with open date 13.01.2017 15:00:12 for example and that is why the DATEDIFF formula didn't work. But I can also test it tomorrow and then tell you;)

2) I calculated Pending time as you said and then wanted to show the number of tickets, which have Pending time < 10 days (for example).

I've used a normal filter and chose there the column Pending time <10.

But it gives me all the tickets with Pending time <10 and includes also the rows with "blank" results, i.e.including also closed&resolved tickets (as you know, I calculated Pending time only for tickets,which do not have status closed&cancelled&resolved,the rest tickets have blank values):

Is it possible to consider this case in the formula, so that making a report I could be sure, that Power BI gives me only results with non-blank values?

Or should I always take one more filter with column Status and filter there closed tickets out?

Thank you very much!

[PavelR · Accepted solution]

@Jolyon

1) DAX takes TODAY as 13.1.2017 0:00:00. If you want to have actual date, then use NOW() function, it is with hours, minutes and seconds.

If you want to see the values, lets create measure

Today = TODAY()

Now = NOW()

and then present it as visual (Card, Tablix etc.).

2) Where have you used that filter? On the page of report? On the righ pane with filters? In the Query editor?

Regards.

Pavel

[Jolyon]

Hi @PavelR,

thanks for the answer!

About filter: yes, I used it on the page of report as visual filter on the right pane. E.g. I wanted to show the number of tickets, which have Pending time < 10 days. And I have to use extra filter on the right pane - to filter the after non-closed tickets.

Regards,

Jolyon

[PavelR]

@Jolyon

You can use page or report level filter, there you can set  Pending time less or equal to whatever number you want, and then you can also use the AND possibility and add next condition as is not blank.

Let me know if it helped.

Regards.

Pavel