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.

az38's avatar
az38
Community Champion
6 years ago

Hi carol_mar 

try

days in between = 
CALCULATE(MAX(Table[date]), ALLEXCEPT(Table, Table[costumer id])) -
CALCULATE(MIN(Table[date]), ALLEXCEPT(Table, Table[costumer id]))

and

number of transaction = 
CALCULATE(Countrows(Table), ALLEXCEPT(Table, Table[costumer id]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

  • carol_mar's avatar
    carol_mar
    Helper I
    6 years ago

    no, it doesn't work, gives me error

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      which solution you tried and what error you got.

      • carol_mar's avatar
        carol_mar
        Helper I
        6 years ago
         

        😪

        this one, why??