Forum Discussion

Atseaukes's avatar
Atseaukes
Frequent Visitor
8 years ago
Solved

Index column based on date, multiple entries excluded based on time

Current colomns        Desired column action id user_id date time   Index 2ac9756d-d46d-42ff-b759-6fb88beaf8f7 1 11-06-18 11:35:40   1 30ac92ed-8e81-4443-aed7-8261eb0a597c ...
  • Atseaukes's avatar
    Atseaukes
    8 years ago

    Step 1:

    Index =

    VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
    RETURN

    DIVIDE (DATEDIFF(UserFirstDate ;'Events Completion'[DateTime];DAY);1)+1

     

    Step 2:

    Rank =
    VAR d = 'Events Completion'[Index]
    VAR c = 'Events Completion'[user_id]
    RETURN
    CALCULATE (
    RANK.EQ ( d; 'Events Completion'[Index]; ASC );
    FILTER ( ALL ( 'Events Completion'); 'Events Completion'[user_id] = c ))

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi  Atseaukes

 

You could try this calculated column which takes into account date & time

 

Column = 
VAR UserFirstDate = MINX(FILTER('Table1','Table1'[user_id] = EARLIER('Table1'[user_id])),'Table1'[date] & " " & 'Table1'[time])
RETURN DIVIDE(DATEDIFF(UserFirstDate ,'Table1'[date] & " " & 'Table1'[time],HOUR),24)+1

Can adjusted as needed

  • Atseaukes's avatar
    Atseaukes
    Frequent Visitor
    8 years ago

    Hi Phil_Seamark,

     

    I used:

    Index =
    VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[date] & " " & 'Events Completion'[time])
    RETURN DIVIDE(DATEDIFF(UserFirstDate ;'Events Completion'[date] & " " & 'Events Completion'[time];HOUR);24)+1

     

    but somehow that didn't work. See the example hereunder.

     

     

    It gave back negative results and decimals which, in addition, are not the same for the same day... 

     

    Any clue?

    • Atseaukes's avatar
      Atseaukes
      Frequent Visitor
      8 years ago

      I made some changes:

      DayOfUse = VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
      RETURN DIVIDE(DATEDIFF(UserFirstDate ;'Events Completion'[DateTime];DAY);1)+1

       

      But this results in the day number after first use. So startdate gives value 1. Second date is af day 10 gives value 10.

      How to change the second (thirth, forth, etc) day into value 2?

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi Atseaukes

         

        Is the time of day important?

         

        Eg, if the first item starts at 2pm (in the afternoon) on a Monday.

         

        In that case, what value would you give to an event that took place at 10am on the following Wednesday, and also to one that took place at 8pm on the same Wednesday?  

         

        Do you need to track that one of those events was less than 48 hours while the other is more than 48 hours to give a different result?  Or should both have the same value because the time component is not important?