Forum Discussion

carol_mar's avatar
carol_mar
Helper I
6 years ago
Solved

date

Hello, I need to manage some date.  I need to calculate the difference (in terms o number days) among dates.  so, I have for each costumer the date related to each transaction they make and I need...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi carol_mar ,

     

     

    //Measures
number of transaction = 
COUNT(Sheet7[transaction amount])

days in between = 
VAR x = MAX(Sheet7[date])
VAR y = MIN(Sheet7[date])
RETURN
DATEDIFF(y,x,DAY)

     

    You must add [costumer id] column into the table chart or the matrix chart, doing this is equivalent to using ALLEXCEPT() function.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
6 years ago

One ) should be before ;Day

 

carol_mar's avatar
carol_mar
Helper I
6 years ago

yes sorry, but still it gives all 0 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    one correct , one is max, and you need view data by client'

    days diff =
    datediff(MIN(Table[date]),MAX(Table[date]), DAY)

    • carol_mar's avatar
      carol_mar
      Helper I
      6 years ago

      and now.. 30 everywhere😪

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        I hope you created it as a measure and using with customer id.

         

        If you want to get that in the table as a column, then you can get a diff from the last date

        datediff = datediff(table[date],maxx(filter(table[cust_id]=earlier(table[cust_id]) && table[date]<earlier(table[date])),table[date]),DAY)