Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

Calculation of days within the same table

Hello

I have a record that brings the date on which a group of online paperwork applications went through every possible state.

Estados.PNG

For the same requestid the table brings as many records as states (content) through which the procedure "SENT, CONTRACT PENDING, FINISHED", etc., passed.

Also in the column (Tramite1) are the different types of possible procedures.

I need to calculate for each request id, how many days elapsed between the SUBMITTED status and the PENDING CONTRACT status, and then be able to draw averages of delay for each type of procedure, and within a certain period of time.

Example: The average delay in days between the SENT status and the PENDING CONTRACT status of savings account openings that reached the last state in June.

Thank you for guiding me how to proceed.

Best regards

Manuel.

  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Syndicate_Admin ,

    Create a column as below:

    datediff = 
var _date=IF('Table'[Contenid]="in analysis",CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Contenid]="approved"&&'Table'[id]=EARLIER('Table'[id])&&'Table'[Date]>EARLIER('Table'[Date]))))
Return
DATEDIFF('Table'[Date],_date,DAY)

    And you will see:

    Check my sample .pbix file attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

6 Replies

  • LuisVillarreal's avatar
    LuisVillarreal
    Frequent Visitor
    5 years ago

    Buenas Manuel, me parece muy interesante tu requerimiento.

     

    No se me ocurre forma de hacerlo desde PBI ya que mi experiencia con DAX es limitada, así que yo lo haría directamente desde el query a la BD, utilizando un subquery más o menos así:

     

    SELECT
    solicitud = id_solicitud,
    primera_fecha = columna_bd,
    DATEDIFF(day, primera_fecha, segunda_fecha - 1) AS [total_dias]
FROM Tabla1
JOIN Tabla2 ON Tabla1.id = Tabla2.id AND Tabla2.etapa = (SELECT MAX(etapa) - 1 AS [etapa] FROM Tabla2 WHERE id_solicitud = solicitud)

     

    Esto lo haría para cada uno de los intervalos entre un status y otro. Lo haría de esa manera asumiendo que tienes una tabla maestro y una tabla detalle. En caso de que lo tengas todo unificado en una sola tabla, el query sería más sencillo.

     

    Espero que te sea de utilidad, saludos cordiales.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      5 years ago

      Hello, thank you very much for the answer.

      I understand that to implement that sclusion you should be able to access the data source. In my case I only have a report that brings the selected information that I detailed above.

      Maybe it can be implemented the same and I'm not realizing it.

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  Syndicate_Admin ,

         

        I cant understand your logic with the sample data provided,I couldnt find the the related columns mentioned in oringinal post,could you pls provide more details to make the issue clear?

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      5 years ago

      Hi Kelly, I add another photo in case it helps to understand what my data is like.

      I have a single table that brings this information.

      What I want to know is the average time it takes to move "Rental Guarantee Opening" requests from one state to another.

      As I show in the image, the "requestid" column brings the identification number of each request.

      For the 12257 (which I point out as an example) I have 3 records.

      I want to calculate the difference of days between the Date of the record that in the column "content" has the value IN ANALYSIS, and the record that has the value APPROVED for the same "requestid". And perform that same calculation for each of the applications that appear in the table so as to be able to know the average of days that take between one state and another the requests that in the column "Tramite1" have the value "Opening of Rental Guarantee".

      Can this be solved with the statement (SELECT) you suggest? I don't see how to do it.

      Tabla solicitudes.PNG

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  Syndicate_Admin ,

        Create a column as below:

        datediff = 
var _date=IF('Table'[Contenid]="in analysis",CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Contenid]="approved"&&'Table'[id]=EARLIER('Table'[id])&&'Table'[Date]>EARLIER('Table'[Date]))))
Return
DATEDIFF('Table'[Date],_date,DAY)

        And you will see:

        Check my sample .pbix file attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!