Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 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.
Solved! Go to Solution.
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!
Hi @Syndicate_Admin ,
Have your checked my reply?
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
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.
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!
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.
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.
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!
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |