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. For the same requestid the table brings as many records as ...
  • 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!

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!