How do I calculate the duration between dates based on "items" and "operations"?

[powerbiusernew]

Hi all

I have a table as shown below, which has users and the items they loan or returns as well as the dates. How do I calculate the durations between say User A which loan Item 1 and return Item 1? (so each return is end of item 1)

[v-diye-msft]

Hi @powerbiusernew 

Do you mean you'd like to calculate the duration between the adjacent loan and Return date by users? Or duration between the first loan date and last return date by users?

[powerbiusernew]

Hi @v-diye-msft , 

I would like to calculate the duration between the adjacent loan and return dates by users based on items (if that make sense). As each user can loan the same or different items multiple times, I would like to calculcate the duration for each loan. So is there some measures that I can create which can allows this happens?

Regards

[v-diye-msft]

Hi @powerbiusernew 

Let me know if you'd like to get this:

Measure = var loandate = CALCULATE(MAX('Table (3)'[Date]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Users],'Table (3)'[Item]),[Operations]="Loan"))
var returndate = CALCULATE(MAX('Table (3)'[Date]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Users],'Table (3)'[Item]),[Operations]="Return"))
Return
CALCULATE(DATEDIFF(loandate,returndate,DAY),ALLEXCEPT('Table (3)','Table (3)'[Item],'Table (3)'[Users]))

But please note that :

1. There 2 records of A, item 2 and loan date, not sure which one should be taken into count.

2. There no records of return date of D under item 1

[powerbiusernew]

HI @v-diye-msft ,

Thanks! I'm actually looking something a little different, so for example in the picture below, when A Loan Item "1" on the 2/6/2019 (MM/DD/YYYY), A Returned it on 2/7/2019, so the duration for that loan is 1 Day. When A Loan Item "2" on 2/13/2019, the return date is 3/17/2019, which should return 32 days.

[v-diye-msft]

Hi @powerbiusernew 

Please kindly check my below answer:

when A Loan Item "1" on the 2/6/2019 (MM/DD/YYYY), A Returned it on 2/7/2019, so the duration for that loan is 1 Day.

When A Loan Item "2" on 2/13/2019, the return date is 3/17/2019, which should return 32 days.

Measure 2 = var loandate = CALCULATE(MIN('Table (3)'[Date]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Users],'Table (3)'[Item]),[Operations]="Loan"))
var returndate = CALCULATE(MIN('Table (3)'[Date]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Users],'Table (3)'[Item]),[Operations]="Return"))
Return
CALCULATE(DATEDIFF(loandate,returndate,DAY),ALLEXCEPT('Table (3)','Table (3)'[Item],'Table (3)'[Users]))

[Ashish_Mathur]

Hi,

To your visual drag User and Item fields.  Write this measure

=1*(CALCULATE(MAX(Data[Date]),Data[Operations]="Loan")-CALCULATE(MAX(Data[Date]),Data[Operations]="Return"))

Hope this helps.

[powerbiusernew]

Hi @Ashish_Mathur ,

Thanks for the help, however it still doesn't return what I want. I know it's a little complicated and I'm scratching my head trying to resolve it.

Maybe I can illustrate it better with another diagram. In this table, you'll see that User A loaned Item '1' two times, once on 2/6/2019 (Returned 2/7/2017), and the second time on the 6/2/2019 (returned 6/5/2019), so what I am trying to do is to find out the duration he loaned it on 2 separate occasion eg. the first time (which should be 1 Day), and the second time should be (3 Days). Hopefully that makes more sense now.

[v-diye-msft · Accepted solution]

Hi @powerbiusernew 

It seems I have nailed it! please kindly check this one:

Every duration appeared in the return date.

Measure 2 = var loandate = CALCULATE(MAX('Table (3)'[Date]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Users],'Table (3)'[Item]),[Operations]="Loan"&&[Date]<=MAX('Table (3)'[Date])))
var returndate = MAX('Table (3)'[Date])
Return
CALCULATE(DATEDIFF(loandate,returndate,DAY),ALLEXCEPT('Table (3)','Table (3)'[Item],'Table (3)'[Users]))

Or you'd like to get this one:

Measure 2 = var loandate = CALCULATE(MAX('Table (3)'[Date]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Users],'Table (3)'[Item]),[Operations]="Loan"&&[Date]<=MAX('Table (3)'[Date])))
var returndate = MAX('Table (3)'[Date])
Return
CALCULATE(DATEDIFF(loandate,returndate,DAY),ALLEXCEPT('Table (3)','Table (3)'[Item],'Table (3)'[Users]))

Measure 3 = var  returndate = CALCULATE(min('Table (3)'[Date]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Users],'Table (3)'[Item]),[Operations]="return"&&[Date]>=MAX('Table (3)'[Date])))
var loandate = MAX('Table (3)'[Date])
Return
CALCULATE(DATEDIFF(loandate,returndate,DAY),ALLEXCEPT('Table (3)','Table (3)'[Item],'Table (3)'[Users]))

Measure 4 = [Measure 2]+[Measure 3]

[Ashish_Mathur]

Hi,

There should be a way (other than date) to differentiate the 2 loans that A has taken.  Something like a Loan Number or Transaction column will help.  Do you have any such column?