Forum Discussion
How do I calculate the duration between dates based on "items" and "operations"?
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]
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
Community Support
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
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.
- Ashish_Mathur
Super User
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.
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
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]))
