Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
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]))
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
no, it doesn't work, gives me error
which solution you tried and what error you got.
😪
this one, why??
One ) should be before ;Day
yes sorry, but still it gives all 0
one correct , one is max, and you need view data by client'
days diff =
datediff(MIN(Table[date]),MAX(Table[date]), DAY)
and now.. 30 everywhere😪
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)
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
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."
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
)
unfortunatly doesn't give any reasonable result. It goes from 0 to negative numbers...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |