Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jolyon
Helper III
Helper III

calculate time with conditions?

Hello all,

need your help please.

 

I have a table with tickets and different status.

25.png

 

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!

 

1 ACCEPTED SOLUTION
PavelR
Solution Specialist
Solution Specialist

@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

View solution in original post

8 REPLIES 8
nickneck
New Member

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
Solution Specialist
Solution Specialist

@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

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
Solution Specialist
Solution Specialist

@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

 

@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):

27.png

 

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
Solution Specialist
Solution Specialist

@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

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
Solution Specialist
Solution Specialist

@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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.