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.

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)

  • carol_mar's avatar
    carol_mar
    Helper I
    6 years ago

    when does the "filter ends?" it says too many arguments passed to the filter function and also.. the first argument of the filter should be a table while in your expression is a column. Am I wrong?

    Thank you for helping

  • carol_mar's avatar
    carol_mar
    Helper I
    6 years ago
     
     

    also, trying to modify something it says now "DAX comparison operations do not support comparing values of ytpe Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    I formatted it, Table name was missing after filter

    datediff(table[date]
		,maxx(filter(table,
					table[cust_id]=earlier(table[cust_id]) 
					&& table[date]<earlier(table[date])
					)
				,table[date]
			)
			,DAY
		)
  • carol_mar's avatar
    carol_mar
    Helper I
    6 years ago

    unfortunatly doesn't give any reasonable result. It goes from 0 to negative numbers...