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]
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.
- 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?
- 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]))
