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 Ite...
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]

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

     

    • powerbiusernew's avatar
      powerbiusernew
      Regular Visitor
      6 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        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.