Forum Discussion
earliest and latest dates
I have a dataset that lists every date a client was staying with us, with a unique customer visit id in another column.
What would be a good way to identify the check-in and check-out dates (and ideally how long they stayed, although I know how to do that from check-in/check-out date) for each unique customer id?
Thanks for your help.
petehorsch , New column
Min Date = minx(filter(Table, [unique customer id] = earlier([unique customer id]) ), [Date])
Max Date = Max (filter(Table, [unique customer id] = earlier([unique customer id]) ), [Date])
measures
Min Date = Min(Table[Date])
Max Date = Min(Table[Date])
3 Replies
- amitchandak
Super User
petehorsch , New column
Min Date = minx(filter(Table, [unique customer id] = earlier([unique customer id]) ), [Date])
Max Date = Max (filter(Table, [unique customer id] = earlier([unique customer id]) ), [Date])
measures
Min Date = Min(Table[Date])
Max Date = Min(Table[Date])
This worked well, thank you. I had to include an extra "x" (maxx) to get the Max Date to work, but then it was perfect.
v-yanjiang-msftCommunity Support
Hi petehorsch ,
According to your description, here's my solution.
Create relationship between the two tables with the client name column. Then put ID column and two Date columns in a visual, one Date column select Earliest and the other select Latest.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
