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

The 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.

Reply
Syndicate_Admin
Administrator
Administrator

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.

1 ACCEPTED 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:

vkellymsft_0-1628762102991.png

Check my sample .pbix file attached.

 

Best Regards,
Kelly

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

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

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.

Tabla solicitudes.PNG

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:

vkellymsft_0-1628762102991.png

Check my sample .pbix file attached.

 

Best Regards,
Kelly

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

LuisVillarreal
Frequent Visitor

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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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