I am struggling to subtract date from previous record date based on record ID

[Anonymous]

I have a situation where I have to calculate the amount of time a record stayed in a particular status. It can only be derived from the previous record.

So basically it is supposed to be "if demand ID is same then calculate how long it stayed in a particular status before it was changed to a different one based on the column "created"

Please note this is log data and there can be multiple changes for one demand ID. Ideally if I can calculate it in "days,hours,minutes,seconds" format, but just days format will work as well.

I am stuck on this for days and not able to get around it. Any help will be really appreciated

Any help will be really appreciated on this.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Also, show the expected result for a few line items.

[Anonymous]

I just updated the file in dropbox folder to show some desired results. I have put in the result for 5 records to give an idea of the desired result I am looking for.

Sharing the link again after updating the excel file.

https://www.dropbox.com/s/w06mt0rr5udx3gx/demandissue.xlsx?dl=0

Really appreciate if you can help with this!

[Anonymous]

@Ashish_Mathur

[Anonymous]

Hi,

Please find below the dropbox link to access the excel version of the file

https://www.dropbox.com/s/w06mt0rr5udx3gx/demandissue.xlsx?dl=0

For some reason, I am not able to attach the excel file directly to this thread.

Really appreciate your help!

Regards

[LivioLanzo]

@Anonymous

could you post a data set that could be copy pasted?

[Anonymous]

HI @LivioLanzo,

Thank you so much for wanting to help me out.

For some reason, I am not able to attach files here so please find the dropbox link for the excel file

https://www.dropbox.com/s/w06mt0rr5udx3gx/demandissue.xlsx?dl=0

I have also put in the desired result set for 5 records to give an idea of what results I am looking for.

If you have any questions, please feel free to reach me out.

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works for most of the cases.  The place where it fails is when you want to compute NOW()-[Created].  It fails to roll over over 24 hours.  See row 73 of the screenshot.  I do not know how to solve that.  May be someone else can help you from here.

=if(ISBLANK(CALCULATE(MIN(Data[Created]),FILTER(Data,Data[Created]>EARLIER(Data[Created])&&Data[DemandId]=EARLIER(Data[DemandId])))),NOW()-[Created],CALCULATE(MIN(Data[Created]),FILTER(Data,Data[Created]>EARLIER(Data[Created])&&Data[DemandId]=EARLIER(Data[DemandId])))-[Created])

I have sorted the Table in ascending order by DemandID

[Anonymous]

Hi @Ashish_Mathur,

I converted the duration value into decimal number which allowed me to get the result as 35.717 meaning the demands stayed open for 35 days and approximately 8 hours which gives me the result I was looking for.

Thank you very much for your help!

[Ashish_Mathur]

You are welcome.

[Anonymous]

@Ashish_Mathur

Is there any way to calculate days seperately maybe? because this formula counts just the days!

[v-lili6-msft]

hi, @Anonymous

You may try to use datediff function to improve your formula as  below:

days = IF (
    ISBLANK (
        CALCULATE (
            MIN ( Data[Created] ),
            FILTER (
                Data,
                Data[Created] > EARLIER ( Data[Created] )
                    && Data[DemandId] = EARLIER ( Data[DemandId] )
            )
        )
    ),
    DATEDIFF([Created],NOW () , DAY),
    DATEDIFF([Created],CALCULATE (
        MIN ( Data[Created] ),
        FILTER (
            Data,
            Data[Created] > EARLIER ( Data[Created] )
                && Data[DemandId] = EARLIER ( Data[DemandId] )
        )
    )
        , DAY)
)

Result:

Best Regards,

Lin

[Anonymous]

Hi @Ashish_Mathur,

Thank you so much for your help. I tested the code and yes it works as I wanted it to and yes the only part which needs to be fixed now is the last record which is to be subtracted from current time.

Thank you very much for your help! I will try to see if I can figure it out from here onwards!

[LivioLanzo]

Could you fill out some more result cells as there are some thing that are not so clear 

[Anonymous]

Hi Livio, 

I can try to explain in this reply. I have two columns DemandID which is a unique identifier for a demand put in by a user and a created date which shows the last time the record was updated.

A new row is entered everytime the demand is updated. So for example

DemandID      Created

DID100            17/10/2018 12:00:00

DID100           18/10/2018 15:05:00

DID101           19/10/2018  21:15:20

So first row is when DID100 was created. 2nd row is when DID100 was updated again. I want to calculate the duration for each row.

The result set will look like this DID100  17/10/2018 12:00:00 | 1 day 3 hours 5 Minutes .

The result is dervied from 2nd record of created - first row of created

For DID101, it should not calculate anything because there isn't any 2nd entry entered yet but the formula should run once there is another entry for DID101.

Let me know if that clears it up.

Apologies! I am not the best at explaining things!