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]
Super UserHi,
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
Community Support
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]