Forum Discussion

powerbiusernew's avatar
powerbiusernew
Regular Visitor
6 years ago
Solved

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

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)

 

 

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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]

9 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      powerbiusernew
      Regular Visitor
      6 years ago

      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's avatar
        v-diye-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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