Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
5 years ago
Solved

Count days between first two dates in same column for same user

Hi ! Been struggling with this and I can't get this to work How can I achieve the last column ? 


idusertrx datedates between 1st and 2nd trx date
111.1.2020 
2121.1.2020 
3122.2.202032
4473.3.2020 
5473.3.20200
624.4.2020 
7125.5.2020 

As a calculated column I'm using functions like EARLIER() but with no success

 

Thank you

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Yggdrasill 

    Add this as a column to your table:

    DaysDiff = 
VAR __USER = [user] RETURN
VAR __MIN = 
MINX(
    CALCULATETABLE(
        TOPN(2, Table1, Table1[trx date], ASC ),
        ALLEXCEPT(Table1,Table1[user])),
     Table1[trx date]
)
VAR __MAX = 
MAXX(
    CALCULATETABLE(
        TOPN(2, Table1, Table1[trx date], ASC ),
        ALLEXCEPT(Table1,Table1[user])),
     Table1[trx date]
)
RETURN
IF(
    [trx date] = __MAX, DATEDIFF( __MIN, __MAX, DAY)
)

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Yggdrasill 

    Add this as a column to your table:

    DaysDiff = 
VAR __USER = [user] RETURN
VAR __MIN = 
MINX(
    CALCULATETABLE(
        TOPN(2, Table1, Table1[trx date], ASC ),
        ALLEXCEPT(Table1,Table1[user])),
     Table1[trx date]
)
VAR __MAX = 
MAXX(
    CALCULATETABLE(
        TOPN(2, Table1, Table1[trx date], ASC ),
        ALLEXCEPT(Table1,Table1[user])),
     Table1[trx date]
)
RETURN
IF(
    [trx date] = __MAX, DATEDIFF( __MIN, __MAX, DAY)
)

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn