date

[carol_mar]

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 id	transaction amount	date
sr1212	1111	4/4/2020
st2455	1223	5/4/2020
sr1212	4321	7/4/2020

so my result should be:

costumer id	number of transaction	days in between
sr1212	2	3
st2455	1	0

Can you help me?

Thank you so much

@az38 

[v-lionel-msft · Accepted solution]

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]

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]))

[az38]

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]

no, it doesn't work, gives me error

[amitchandak]

which solution you tried and what error you got.

[carol_mar]

😪

this one, why??

[amitchandak]

One ) should be before ;Day

[carol_mar]

yes sorry, but still it gives all 0 

[amitchandak]

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

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

[carol_mar]

and now.. 30 everywhere😪

[amitchandak]

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]

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]

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]

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]

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