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!

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!