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 to know how many days are in between those days. So i would need to do something like:

 

4/4/2019 - 7/4/2019 = 3 days

 

and this calculation has to be done for an entire coloumn. 

 

My table look like something about:

 

costumer idtransaction amountdate
sr121211114/4/2020
st245512235/4/2020
sr121243217/4/2020

 

so my result should be:

 

costumer idnumber of transactiondays in between 
sr121223
st245510

 

Can you help me?

 

Thank you so much

 

az38 

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.

15 Replies

  • 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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try

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

    or

    calculate(days diff =
    datediff(MIN(Table[date]),MIN(Table[date]), DAY),values(Table[costumer id]))

     

    number of trasactions = CALCULATE(count(Table[costumer id]))

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    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.