Forum Discussion

Jolyon's avatar
Jolyon
Icon for Helper III rankHelper III
9 years ago
Solved

calculate time with conditions?

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 cl...
Need Help
  • PavelR's avatar
    PavelR
    9 years ago

    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

PavelR's avatar
PavelR
Icon for Solution Specialist rankSolution Specialist
9 years ago

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

    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's avatar
      PavelR
      Icon for Solution Specialist rankSolution Specialist
      9 years ago

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

        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!